mysql - Possible to improve the performance of this SQL query? -

i have table has on 100,000,000 rows , have query looks this:

select     count(if(created_at >= '2015-07-01 00:00:00', 1, null)) 'monthly',     count(if(created_at >= '2015-07-26 00:00:00', 1, null)) 'weekly',     count(if(created_at >= '2015-06-30 07:57:56', 1, null)) '30day',     count(if(created_at >= '2015-07-29 17:03:44', 1, null)) 'recent'     items     user_id = 123456; 

the table looks so:

create table `items` (    `user_id` int(11) not null,    `item_id` int(11) not null,    `created_at` timestamp not null default '0000-00-00 00:00:00',     primary key (`user_id`,`item_id`),     key `user_id` (`user_id`,`created_at`),     key `created_at` (`created_at`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci; 

the explain looks harmless, minus massive row counts:

1   simple  items   ref primary,user_id user_id 4   const   559864  using index 

i use query gather counts specific user 4 segments of time. there smarter/faster way obtain same data or option tally these new rows put table?

i add index on created_at field:

alter table items add index idx_created_at (created_at) 

or (as thomas suggested) since filtering user_id composite index on created_at , user_id:

alter table items add index idx_user_created_at (user_id, created_at) 

and write query as:

select 'monthly' description, count(*) cnt items created_at >= '2015-07-01 00:00:00' , user_id = 123456  union  select 'weekly' description, count(*) cnt items created_at >= '2015-07-26 00:00:00' , user_id = 123456  union  select '30day' description, count(*) cnt items created_at >= '2015-06-30 07:57:56' , user_id = 123456  union  select 'recent' description, count(*) cnt items created_at >= '2015-07-29 17:03:44' , user_id = 123456 

yes, output little different. or can use inline queries:

select   (select count(*) items created_at>=... , user_id=...) 'monthly',   (select count(*) items created_at>=... , user_id=...) 'weekly',   ... 

and if want average, use subquery:

select   monthly,   weekly,   monthly / total,   weekly / total (   select     (select count(*) items created_at>=... , user_id=...) 'monthly',     (select count(*) items created_at>=... , user_id=...) 'weekly',     ...,     (select count(*) items user_id=...) total ) s