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

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

Anything I could do to avoid the database fallout?

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;";