Mysql Query Too Slow!

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Mysql Query Too Slow!

Postby karadam » 21. October 2009 11:03

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
karadam
 
Posts: 2
Joined: 21. October 2009 10:57

Re: Mysql Query Too Slow!

Postby Nobbie » 21. October 2009 12:31

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.
Nobbie
 
Posts: 8779
Joined: 09. March 2008 13:04

Re: Mysql Query Too Slow!

Postby Wiedmann » 21. October 2009 17:57

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.
Wiedmann
AF Moderator
 
Posts: 17106
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Re: Mysql Query Too Slow!

Postby Nobbie » 21. October 2009 22:00

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.
Nobbie
 
Posts: 8779
Joined: 09. March 2008 13:04

Re: Mysql Query Too Slow!

Postby karadam » 22. October 2009 10:41

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...
karadam
 
Posts: 2
Joined: 21. October 2009 10:57

Re: Mysql Query Too Slow!

Postby Nobbie » 22. October 2009 12:26

>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).
Nobbie
 
Posts: 8779
Joined: 09. March 2008 13:04


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 21 guests