Mysql Order By DESC slow for certain conditions only

I have a database of a million rows which isn't a lot. They're all sorted by cities with a city_id (indexed). I wanted to show the most recent post with:

SELECT * FROM table FORCE INDEX开发者_开发知识库(PRIMARY, city_id) WHERE city_id=1 ORDER BY 'id' DESC LIMIT 0,4

id is also labeled primary. Before adding the force index it took 5.9 seconds. I found that solution on SO and it worked great. The query now takes 0.02 seconds.

The problem is that this seems to only worked in city_id 1 when I change that city to 2 or 3 or anything else it seems to be back to 6 seconds.

I'm not certain how mysql works. Does it index better on frequent queries or am I missing something here.

Do an explain on your query (with and without the force):

explain SELECT * FROM table WHERE city_id=1 ORDER BY 'id' DESC LIMIT 0,4

and see what mysql tells you about the cost of the use of a certain index. Concerning your indexing strategy and your force: MySQL loves combined indexes and is usually not very good at combining them itself and the primary indexing is always on, so no need to specify it. Concerning your statement I would use something like this and see if it improves the performance:

select * from table use index(city_id) where city_id=1 ORDER BY 'id' 
DESC LIMIT 0,4

And always keep in mind: measuring the time of a statement on cmdline more than once will always let the cache kickin so it's pretty useless (and maybe that's the reason you get a bad performance if you change the city_id to a different value).

You will find a lot of good tips and performance hints in the MySQL Performance Blog.