MySQL Forums
Forum List  »  Knowledge Base

Mysql query optimization
Posted by: Rani Jonnala
Date: February 10, 2010 05:55PM

Hi,

I have a following table structure with 18 mil records in it. What is the best way to optimize the following query

CREATE TABLE `db5`.`table1` (
Id int(12),
`column1` datetime DEFAULT NULL,
`column2` int(11) NOT NULL,
`column3` int(11) NOT NULL,
`column4` int(11) DEFAULT NULL
PRIMARY KEY (`id`),
KEY `table1_column1` (`column1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


explain select
(case when column1 >= curdate() - interval 1 day then "d1"
when column1 >= curdate() - interval 2 day and column1 < curdate()-interval 1 day then "d2"
when column1 >= curdate() - interval 1 day -interval 1 month then "d3"
when column1 >= curdate() - interval 1 day -interval 2 month and column1
< curdate()- interval 1 day-interval 1 month then "d4" end ) h0 ,
count( 1 ) h1,
sum(case when column4 not in ( 10,234 ) then 1 else 0 end) h2,

sum(case when column3 >= column2 then 1 else 0 end) h3,now() h4
from table1 where column1>=curdate()-interval 2 month-interval 1 day and column1<curdate() group by h0


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table1 ALL table1_column1 18987019 Using where; Using temporary; Using filesort

Any advise is appreciated

Regards,
JR

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql query optimization
1866
February 10, 2010 05:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.