MySQL First Search Take 30 Seconds

[MrFish]

I have a function that sometimes takes 0-1 seconds to run but other times takes 30 seconds to run. It usually takes 25-30 seconds to run if it hasn't been run in a while and any time after that it's very quick. I have split the function up into seconds and I'm recording each section to find when and where the function is taking too long. I've narrowed it down to one query. I'd like to know why this query is taking so long when it hasn't been used for a while but then afterwards runs quickly.

$result = mysql_query("SELECT DISTINCT city FROM world WHERE latitude >= '$startLat' AND latitude <= '$endLat' AND longitude >= '$startLong' AND longitude <= '$endLong'");

(second)The world database is queried even once before this and doesn't have this kind of lag.

$result = mysql_query("SELECT latitude, longitude FROM world WHERE region='$state' AND city='$city' LIMIT 0, 1");

(first)And then afterwards this query may be searched up to 50 times-

$r = mysql_query("SELECT * FROM world WHERE city='$cityName' AND latitude >= '$startLat' AND latitude <= '$endLat' AND longitude >= '$startLong' AND longitude <= '$endLong' LIMIT 0, 1");

(third)Is DISTINCT doing anything to the query that is causing it to lag like this? Currently that database has every field indexed since it's not ever updated or added to.

[justsomeguy]

You can try testing without distinct if you think that's an issue. The time differences may be due to caching. The other queries are probably faster because they're looking for specific values instead of a range.

[MrFish]

Well the DISTINCT bit is going to be necessary and I do search a range on others and even the one that is ran up to 50 times. So if it's a cache thing do you think I should setup a cron that does a similar search every 30 minutes just to keep it alive?

[justsomeguy]

Well the DISTINCT bit is going to be necessary and I do search a range on others and even the one that is ran up to 50 times.

Don't you think it's still useful to test? If you know what the issue is then you can look for workarounds. If you don't know what the issue is you're only guessing. I mean, you specifically asked if it was an issue with distinct, so isn't it worth a test? That's typically the only way to find out (or, at least, the easiest). The other ranges all get narrowed by a specific field, if you're searching for Paris and use that as the city name in the WHERE clause, then the other conditions are only searching through the results where the city is Paris, not the entire table. The other query searches for a range in the entire table. If it has a lot of records that might be an issue.

So if it's a cache thing do you think I should setup a cron that does a similar search every 30 minutes just to keep it alive?

I'm not sure if a cron job is the solution, if you can verify it's a cache issue then you might want to look into the cache settings for the database. You might need to do research into how the storage engine you're using caches a table where every column is indexed.