Optimize MySQL count query with JOIN

Question

I have a query that takes about 20 seconds, I would like to understand if there is a way to optimize it. Table 1:

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9845765 ;

And table 2:

CREATE TABLE IF NOT EXISTS `access` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `session_id ` (`session_id `)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9467799 ;

Now, what I am trying to do is to count all the access connected to all sessions about one user, so my query is:

SELECT COUNT(*)
FROM access
INNER JOIN sessions ON access.session_id=session.id
WHERE session.user_id='6';

It takes almost 20 seconds...and for user_id 6 there are about 3 millions sessions stored. There is anything I can do to optimize that query?

Put an index on column session_id in table access.

simon at rcl
– simon at rcl

2014-01-10 16:49:15 +00:00
Commented Jan 10, 2014 at 16:49 — simon at rcl
– simon at rcl, Commented Jan 10, 2014 at 16:49

Joel Coehoorn · Accepted Answer · 2014-01-10 17:02:33Z

1

Change this line from the session table:

KEY `user_id` (`user_id`)

To this:

KEY `user_id` (`user_id`, `id`)

What this will do for you is allow you to complete the query from the index, without going back to the raw table. As it is, you need to do an index scan on the session table for your user_id, and for each item go back to the table to find the id for the join to the access table. By including the id in the index, you can skip going back to the table.

Sadly, this will make your inserts slower into that table, and it seems like this may be a bid deal, given just one user has 3 millions sessions. Sql Server and Oracle would address this by allowing you to include the id column in your index, without actually indexing on it, saving a little work at insert time, and also by allowing you specify a lower fill factor for the index, reducing the need to re-build or re-order the indexes at insert, but MySql doesn't support these.

edited Jan 10, 2014 at 17:02

answered Jan 10, 2014 at 16:48

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Coehoorn Over a year ago

Side note: this shouldn't matter for an inner join, but my gut instinct tells me that you have the table orders backwards: that what you really want to do is start from sessions and join to access, rather than start from access and join to sessions. Again, this really shouldn't matter (hence only including it as a comment), but maybe, just maybe, you can trick the query engine into a better execution plan like this.

Collectives™ on Stack Overflow

Optimize MySQL count query with JOIN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related