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

Question

Hello,

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?

Answer 1

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?

Answer 2

Add an index on user.

Answer 3

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.