Mysql Query Too Slow!

[karadam]

hi guys,

we have problem @ xampp' s mysql. we have 250k rows @ mysql. we r starting a query like below and query turning at 10 sec or more. do u have any suggestion? thx...

select PROTOCOL,DEST_PORT,count(ID) as c from ip_port_protocol Where DATE(CREATION_DATE) between ADDDATE(DATE(NOW()),interval -1 month) and DATE(NOW()) Group By ip_port_protocol.PROTOCOL,DEST_PORT Order BY COUNT(ID) desc LIMIT 0, 5

[Nobbie]

Why do you need DATE()-Function in your query? The NOW()-Function already returns a DATE Format and CREATION_DATE *should* already be a DATE Format.

Try to run that query without DATE-Function Call and additionally try to create an Index over CREATION_DATE.

[Wiedmann]

Why do you need DATE()-Function in your query?

Because he is only interested in the date, but not at the time.

CREATION_DATE = 2009-09-01 01:00:00
NOW = 2009-10-01 02:00:00
INTERVAL (-1 month) = 2009-09-01 02:00:00 - 2009-10-01 02:00:00
--> CREATION_DATE is not in interval.

but:
CREATION_DATE = 2009-09-01
NOW = 2009-10-01
INTERVAL (-1 month) = 2009-09-01 - 2009-10-01
--> CREATION_DATE is in interval.

[Nobbie]

Because he is only interested in the date, but not at the time.

If so, he should store a DATE, not a DATE_TIME.

If there is any chance to optimize that query, he should change "CREATION_DATE" to DATE-Format (and store CREATION_TIME for example in another column if its needed somewhere else) and try to put an Index on CREATION_DATE.

Only an index could speed up that huge query.

[karadam]

Thx guys for fast response but i don t have a problem with creation date, when i m running script below query taking 0.001

SELECT PROTOCOL, DEST_PORT, count( ID ) AS c
FROM ip_port_protocol
WHERE CREATION_DATE
BETWEEN ADDDATE( NOW( ) , INTERVAL -1
MONTH )
AND NOW( )
GROUP BY ip_port_protocol.PROTOCOL, DEST_PORT
LIMIT 0 , 30

than if i try to run the script below its taking over 2,000 sec or more.... i think problem on order by

SELECT PROTOCOL, DEST_PORT, count( ID ) AS c
FROM ip_port_protocol
WHERE CREATION_DATE
BETWEEN ADDDATE( NOW( ) , INTERVAL -1
MONTH )
AND NOW( )
GROUP BY ip_port_protocol.PROTOCOL, DEST_PORT
ORDER BY COUNT( ID ) DESC
LIMIT 0 , 5

not: i have an index over, protocol, dest_port, id and creation date...

[Nobbie]

>i think problem on order by

Yes. Seems to be a large amount of rows and the ORDER is based on a dynamically created value COUNT(ID).