MySQL Performance: MySQL => Query slow first time, then fast

Members

Help

Home

Home » Performance » MySQL » Query slow first time, then fast

Show: Today's Messages :: Show Polls :: Message Navigator

Query slow first time, then fast [message #1786]

Tue, 11 September 2007 04:29

razdaman
Messages: 26
Registered: May 2007

Junior Member

Hello

I have this query:
SELECT SQL_CALC_FOUND_ROWS
m.subject,
c.club_id,
c.name,
ml.link_id,
ml.status, ml.`to`
FROM mails_links AS ml
JOIN mails AS m ON m.mail_id = ml.mail_id
LEFT JOIN clubs AS c ON c.club_id = ml.`from`
WHERE
ml.`to` = '139986' AND
ml.directory = 'INBOX'
ORDER BY m.mail_id DESC
LIMIT 0, 20

The query runs in 1,2-1,5s first time it is run. After this the query runs in 0,01s even though SQL_NO_CACHE is used.

If I wait some time, say an hour, the query is slow again. I'm pretty sure a COUNT(*) on mail_links would be better (as Peter writes here), but in order to be sure I need to know why the above is slow en the first and the gets fast... and slow again.

My best guess is that the index data needed to process the query is purged out of memory once in a while. It is fast when the index data is in memory and slow when it is not. However, this explanation seems a little weird (but still plausible as a lot of tables are fighting for the memory) as the entire index in only around ~80-90mb.

Tables "mail_links", "mails" are MyISAM and "clubs" are InnoDB. I'm running mysql 5.0.45 on debian.

What do you think? Why is it slow the first time?

[Updated on: Tue, 11 September 2007 04:37]

Report message to a moderator

Re: Query slow first time, then fast [message #1788 is a reply to message #1786 ]

Tue, 11 September 2007 08:47

scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada

Member

Could you please provide us with the results of EXPLAIN command for your query?

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator

Re: Query slow first time, then fast [message #1789 is a reply to message #1786 ]

Tue, 11 September 2007 10:07

razdaman
Messages: 26
Registered: May 2007

Junior Member

Here it is:

1  	SIMPLE  	ml  	ref  	to  	to  	3  	const  	1219  	Using where; Using temporary; Using filesort
1 	SIMPLE 	c 	eq_ref 	PRIMARY 	PRIMARY 	3 	vman.ml.from 	1 	 
1 	SIMPLE 	m 	eq_ref 	PRIMARY 	PRIMARY 	3 	vman.ml.mail_id 	1

Report message to a moderator

Re: Query slow first time, then fast [message #1790 is a reply to message #1789 ]

Tue, 11 September 2007 10:19

scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada

Member

1) Try to use ml.mail_id in order by clause
2) Add key (to, directory, mail_id) to mail_links table.

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator

Re: Query slow first time, then fast [message #1802 is a reply to message #1786 ]

Wed, 12 September 2007 12:07

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Ony typical mistake about SQL_NO_CACHE is thinking it fixes all caches in fact it does not. It only bypasses query cache and there is innodb buffer pool OS caches etc.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Query slow first time, then fast [message #2605 is a reply to message #1786 ]

Fri, 08 February 2008 05:54

razdaman
Messages: 26
Registered: May 2007

Junior Member

... I got it working - thank you! Smile

Report message to a moderator

Previous Topic:	Telnet issues after fresh install - Connection to host lost
Next Topic:	Another temorary table problem

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 21:47:23 EST 2009

Total time taken to generate the page: 0.01228 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.