MySQL: Which indexes to use for a simple range select?

Question

I have a table with ~30 million rows ( and growing! ) and currently i have some problems with a simple range select.

The query, looks like this one:

SELECT SUM( CEIL( dlvSize / 100 ) ) as numItems
FROM log
WHERE timeLogged BETWEEN 1000000 AND 2000000
AND user = 'example'</pre>

It takes minutes to finish and i think that the solution would be at the indexes that i'm using. Here is the result of explain:

+----+-------------+-------+-------+---------------------------------+---------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys                   | key     | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------------------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | log   | range | PRIMARY,timeLogged              | PRIMARY | 4       | NULL | 11839754 | Using where | 
+----+-------------+-------+-------+---------------------------------+---------+---------+------+----------+-------------+

My table structure is this one ( reduced to make it fit better on the problem ):

CREATE TABLE IF NOT EXISTS `log` (
  `origDomain` varchar(64) NOT NULL default '0',
  `timeLogged` int(11) NOT NULL default '0',
  `orig` varchar(128) NOT NULL default '',
  `rcpt` varchar(128) NOT NULL default '',
  `dlvSize` varchar(255) default NULL,
  `user` varchar(255) default NULL,
  PRIMARY KEY  (`timeLogged`,`orig`,`rcpt`),
  KEY `timeLogged` (`timeLogged`),
  KEY `orig` (`orig`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Any ideas of what can I do to optimize this query or indexes on my table?

score 3 · Accepted Answer · edited May 23 '17 at 12:30

3

You may want to try adding a composite index on (user, timeLogged):

CREATE TABLE IF NOT EXISTS `log` (
  ...
  KEY `user_timeLogged` (user, timeLogged),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Related Stack Overflow post:

Database: When should I use a composite index?

edited May 23 '17 at 12:30

Community

1
1

answered Sep 14 '10 at 14:42

Daniel Vassallo

337,827
72
505
443

2

This, and also you can drop the index on `timeLogged` since that's already taking part in the left-hand-side of the primary key. – bobince Sep 14 '10 at 14:59
Thank you, this reduced the query time to less than 1 minute. I will keep searching for other options as i would like to have this in less than 10 seconds, at least. – Fernando Sep 14 '10 at 20:22
Why isn't the key `timeLogged_user (timeLogged, user)`. I thought indexes work left to right? – Chris Muench Apr 03 '14 at 02:20

score 1 · Answer 2 · answered Sep 14 '10 at 15:15

In addition to the suggestions made by the other answers, I note that you have a column user in the table which is a varchar(255). If this refers to a column in a table of users, then 1) it would most likely to far more efficient to add an integer ID column to that table, and use that as the primary key and as a referencing column in other tables; 2) you are using InnoDB, so why not take advantage of the foreign key capabilities it offers?

Consider that if you index by a varchar(n) column, it is treated like a char(n) in the index, so each row of your current primary key takes up 4 + 128 + 128 = 260 bytes in the index.

score 0 · Answer 3 · answered Sep 14 '10 at 14:43

0

Add an index on user.

answered Sep 14 '10 at 14:43

D'Arcy Rittich

167,292
40
290
283

1

Daniel's suggestion of adding an index on (user, timelogged) is probably best (recall that an index on `(A, B)` may also be used as an index on `(A)`). – Hammerite Sep 14 '10 at 14:51
Hmm, well timelogged is already indexed as primary key, so it may not be necessary to incur the additional overhead by using it in a composite index as well. As always, testing is the best approach to validating questions like this. – D'Arcy Rittich Sep 14 '10 at 15:02

MySQL: Which indexes to use for a simple range select?

3 Answers3