Mysql with big tables: how to optmize this query?

Question

I have a table using InnoDB that stores all messages sent by my system. Currently the table have 40 million rows and grows 3/4 million per month.

My query is basically to select messages sent from an user and within a data range. Here is a simplistic create table:

CREATE TABLE `log` (
  `id` int(10) NOT NULL DEFAULT '0',
  `type` varchar(10) NOT NULL DEFAULT '',
  `timeLogged` int(11) NOT NULL DEFAULT '0',
  `orig` varchar(128) NOT NULL DEFAULT '',
  `rcpt` varchar(128) NOT NULL DEFAULT '',
  `user` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `timeLogged` (`timeLogged`),
  KEY `user` (`user`),
  KEY `user_timeLogged` (`user`,`timeLogged`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note: I have individual indexes too because of other queries.

Query looks like this:

SELECT COUNT(*) FROM log WHERE timeLogged BETWEEN 1282878000 AND 1382878000 AND user = 20

The issue is that this query takes from 2 minutes to 10 minutes, depending of user and server load which is too much time to wait for a page to load. I have mysql cache enabled and cache in application, but the problem is that when user search for new ranges, it won't hit cache.

My question are:

• Would changing user_timeLogged index make any difference?
• Is this a problem with MySQL and big databases? I mean, does Oracle or other DBs also suffer from this problem?

AFAIK, my indexes are correctly created and this query shouldn't take so long.

Thanks for anyone who help!

Answer 1

you're using innodb but not taking full advantage of your innodb clustered index (primary key) as it looks like your typical query is of the form:

select <fields> from <table> where user_id = x and <datefield> between y and z

not

select <fields> from <table> where id = x 

the following article should help you optimise your table design for your query.

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

If you understand the article correctly you should find youself with something like the following:

drop table if exists user_log;
create table user_log
(
user_id int unsigned not null,
created_date datetime not null, 
log_type_id tinyint unsigned not null default 0, -- 1 byte vs varchar(10)
...
...
primary key (user_id, created_date, log_type_id)
)
engine=innodb;

Here's some query performance stats from the above design:

Counts

select count(*) as counter from user_log

counter
=======
37770394

select count(*) as counter from user_log where 
 created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'

counter
=======
35547897

User and date based queries (all queries run with cold buffers)

select count(*) as counter from user_log where user_id = 4755

counter
=======
7624

runtime = 0.215 secs


select count(*) as counter from user_log where 
 user_id = 4755 and created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'

counter
=======
7404

runtime = 0.015 secs

select 
 user_id,
 created_date,
 count(*) as counter
from 
 user_log 
where 
 user_id = 4755 and created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'
group by
 user_id, created_date
order by
 counter desc
limit 10;

runtime = 0.031 secs

Hope this helps :)

Answer 2

COUNT(*) is not loading from the table cache because you have a WHERE clause, using EXPLAIN as @jason mentioned, try changing it to COUNT(id) and see if that helps.

I could be wrong, but I also think that your indexes have to be in the same order as your WHERE clause. Since your WHERE clause uses timeLogged before user then your index should be KEYuser_timeLogged(timeLogged,user)`

Again, EXPLAIN will tell you whether this index change makes a difference.