MySQL - How can I improve these queries? -

MySQL - How can I improve these queries? -

first one:

select month(timestamp) d, count(*) c table year(timestamp)=2012 , status = 1 grouping month(timestamp)

one of issues i'm facing 1 have run multiple queries utilize different values status. there way combine them one? in 1 column have counts when status=1 , column when status=2, etc.

second one:

select count(*) c , month(timestamp) t ( select t.adminid, timestamp table1 t left bring together admins on a.adminid=t.adminid year(timestamp)=2012 grouping t.adminid, date(timestamp) order timestamp desc ) grouping month(timestamp) order month(timestamp) asc;

a nested query, not sure if can improve on this. i'm running 1 on 2 tables, 1 has ~35k rows , 1 has ~300k rows. takes half sec first table , 4-5 seconds second.

these might help:

first one:

select month(timestamp) d, sum(case when status=1 1 else 0 end) status1count, sum(case when status=2 1 else 0 end) status2count, sum(case when status=3 1 else 0 end) status3count `table` timestamp between '2012-01-01 00:00:00' , '2012-12-31 23:59:59' , status in (1,2,3) grouping month(timestamp);

second one:

make sure there index on timestamp column , create sure not run conversion functions e.g. month(timestamp) on indexed column. somthing like:

select count(*) c , a.m t ( select t.adminid, timestamp, month(timestamp) m table1 t left bring together admins on a.adminid=t.adminid timestamp between '2012-01-01 00:00:00' , '2012-12-31 23:59:59' grouping t.adminid, date(timestamp) order timestamp desc ) grouping a.m order a.m asc;

second 1 bit tricky since not have info in front end of me can't see db access path!

mysql