Confused - Query Time

[jaymc]

I have been watching the mysql process list and often find some queries taking 50 seconds

 

I then copy that query and run it on a test server and it takes 0.3 seconds

 

There is no cache involved as that query has never been run before on the test server

 

Can you think of a reason why a query can be instant and then another time take 50 seconds.

 

By the way, this query was the highest running one, so its not as if there was a query running ahead of it (51 seconds) Which had locked the table.

 

Table is InnoDB

 

Perhaps its not reading something from memory, so resorts direct to disk hence 50 seconds rather than 0.3

 

Please advise why this may happen

[fenway]

How do you know for certain that there's no lock contention?

[jaymc]

I take snap shots of the process list, this was the highest running in the list in terms of time

[fenway]

Well, to be sure it's not a caching issue, use "SELECT SQL_NO_CACHE .... FROM ....."

 

What's the EXPLAIN like?

 

 

[jaymc]

EXPLAIN  SELECT SQL_NO_CACHE f.user, f.friend, f.gender, f.x, f.timestamp, c.propic AS image

FROM friends f

INNER JOIN cache c ON f.friend = c.username

WHERE f.user = 'jaymc'

AND f.gender = 'Male'

ORDER BY f.x DESC , f.timestamp DESC

LIMIT 0 , 10

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1  	SIMPLE  	f  	ref  	user,friend,user_2  	user_2  	27  	const  	2994  	Using where; Using index; Using filesort
1 	SIMPLE 	c 	eq_ref 	PRIMARY 	PRIMARY 	27 	mysql.f.friend 	1 

 

5 million rows by the way

[fenway]

I think I remember you trying to optimize this query earlier?

[jaymc]

earlier? Try the last month

 

#mysql irc dudes tell me it's impossible due to table size

 

I've now discovered that sometimes it will execute in less than a second, others 12 seconds

 

That's with no cache by the way.