Mysql with big tables: how to optmize this query? -

i have table using innodb stores messages sent system. table have 40 million rows , grows 3/4 million per month.

my query select messages sent user , within data range. here 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: have individual indexes because of other queries.

query looks this:

select count(*) log timelogged between 1282878000 , 1382878000 , user = 20

the issue query takes 2 minutes 10 minutes, depending of user , server load time wait page load. have mysql cache enabled , cache in application, problem when user search new ranges, won't hit cache.

my question are:

• would changing user_timelogged index make difference?
• is problem mysql , big databases? mean, oracle or other dbs suffer problem?

afaik, indexes correctly created , query shouldn't take long.

thanks help!

you're using innodb not taking full advantage of innodb clustered index (primary key) looks typical query of form:

select <fields> <table> user_id = x , <datefield> between y , z 

not

select <fields> <table> id = x  

the following article should optimise table design query.

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

if understand article correctly should find youself 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 query performance stats above design:

counts

select count(*) counter user_log  counter ======= 37770394  select count(*) counter user_log   created_date between '2010-09-01 00:00:00' , '2010-11-30 00:00:00'  counter ======= 35547897 

user , date based queries (all queries run cold buffers)

select count(*) counter user_log user_id = 4755  counter ======= 7624  runtime = 0.215 secs   select count(*) counter user_log   user_id = 4755 , created_date between '2010-09-01 00:00:00' , '2010-11-30 00:00:00'  counter ======= 7404  runtime = 0.015 secs  select   user_id,  created_date,  count(*) counter   user_log    user_id = 4755 , created_date between '2010-09-01 00:00:00' , '2010-11-30 00:00:00' group  user_id, created_date order  counter desc limit 10;  runtime = 0.031 secs 

hope helps :)