Slow on first few queries, then really fast

[mortenj]

I am a Mysql novice.

 

After deploying the PHP/Mysql code on my host, Site5, a strange pattern emerged.

 

When I'm using my web service, all queries are fast. But then, if no users have been using it for a few hours (I guess), the first query after that period is extremely slow - so slow it ends in a timeout.

 

The database is rather large. Here's the table in question.

 

Table B (madsted)
Format  	fixed
Collation 	utf8_general_ci
Rows 	49,393
Row length ø 	4,518
Row size ø 	4,565 B

 

And the sql query:

 

$sql="SELECT navn1, lat, lon, postnr, virksomhedstype, branchekode, adresse1, 
degrees(acos(
sin( radians(madsted.lat) )
* sin( radians(".$lat."))
+ cos( radians(madsted.lat))
* cos( radians(".$lat."))
* cos( radians(madsted.lon - ".$lon.") ) 
) ) * 69.09 * 1.609344 * 1000 as meterdistance
FROM madsted
WHERE degrees(acos(
sin( radians(madsted.lat) )
* sin( radians(".$lat."))
+ cos( radians(madsted.lat))
* cos( radians(".$lat."))     	
* cos( radians(madsted.lon - ".$lon.") ) 
) ) * 69.09 < (10 * 1.609344)   
AND (
	virksomhedstype = 'Detail'
	)       
AND (
	branchekode <> '5552B'
	and branchekode <> '5551A'
	)	
order by meterdistance limit 20;";   

 

 

[ShogunWarrior]

It's probably the query cache kicking in, and expiring after a few hours and having to be re-updated once after the interval.

[mortenj]

Anything I could do to avoid the database fallout?

[fenway]

Not really, I have no idea what indexes might be used, but you could try and keep them loaded.

[mortenj]

Can anyone explain how I keep the indexes loaded?

 

I've done an explain for each query. 

 

EXPLAIN SELECT navn1, lat, lon, postnr, virksomhedstype, branchekode, adresse1, degrees( acos( sin( radians( madsted.lat ) ) * sin( radians( 55.66809314904700300985 ) ) + cos( radians( madsted.lat ) ) * cos( radians( 55.66809314904700300985 ) ) * cos( radians( madsted.lon - 12.54388122614099998486 ) ) ) ) * 69.09 * 1.609344 *1000 AS meterdistance
FROM madsted
WHERE degrees( acos( sin( radians( madsted.lat ) ) * sin( radians( 55.66809314904700300985 ) ) + cos( radians( madsted.lat ) ) * cos( radians( 55.66809314904700300985 ) ) * cos( radians( madsted.lon - 12.54388122614099998486 ) ) ) ) * 69.09 < ( 10 * 1.609344 )
AND (
virksomhedstype = 'Detail'
)
AND (
branchekode <> '5552B'
AND branchekode <> '5551A'
)
ORDER BY meterdistance
LIMIT 20 ;

 

And the output

 

1  	SIMPLE  	madsted  	range  	branchekodeindex  	branchekodeindex  	61  	NULL  	41719  	Using where; Using filesort

 

 

Next query

 

EXPLAIN SELECT *
FROM madsted
WHERE navn1 LIKE '%sim%'
AND (
lat IS NOT NULL
)
LIMIT 0 , 100

 

and its explain output

 

1  	SIMPLE  	madsted  	range  	latindex  	latindex  	24  	NULL  	43766  	Using where

 

 

[ShogunWarrior]

Could you fill the table with the cos() and sin() values for each row? I am willing to bet that it would cut out ALOT of work.

From what I see in the query it has to compute sin, cos and rad->degree values for each row and that must take up quite a bit of query time. If you could pre-compute the values AFAIK it would remove alot of computation and you could index those columns, at the moment no indexes are being used.

[mortenj]

I think you're right. But the calculations are done with varying variables.

 

Anyway, the strange thing is that this query is slow, too - when performed first - despite its simplicity.

 

SELECT navn1, lat, lon
FROM madsted
WHERE navn1 LIKE '%sim%'
AND (
lat IS NOT NULL
)
LIMIT 0 , 100

[fenway]

Well, a great many rows are being examined... that's the first problem (unless your result set is really that large, which I doubt).  Also, the filesort doesn't help, maybe a covering index?