I have a query on my homepage that is getting slower and slower as my database table grows larger.

tablename = tweets_cache rows = 572,327

this is the query I'm currently using that is slow, over 5 seconds.

SELECT * FROM tweets_cache t WHERE t.province='' AND t.mp='0' ORDER BY t.published DESC LIMIT 50;

If I take out either the WHERE or the ORDER BY, then the query is super fast 0.016 seconds.

I have the following indexes on the tweets_cache table. PRIMARY published mp category province author

So i'm not sure why its not using the indexes since mp, provice and published all have indexes? Doing a profile of the query shows that its not using an index to sort the query and is using filesort which is really slow.

possible_keys = mp,province Extra = Using where; Using filesort

I tried adding a new multie-colum index with "profiles & mp". The explain shows that this new index listed under "possible_keys" and "key", but the query time is unchanged, still over 5 seconds.

Here is a screenshot of the profiler info on the query. http://i355.photobucket.com/albums/r469/canadaka_bucket/slow_query_profile.png

Something weird, I made a dump of my database to test on my local desktop so i don't screw up the live site. The same query on my local runs super fast, milliseconds. So I copied all the same mysql startup variables from the server to my local to make sure there wasn't some setting that might be causing this. But even after that the local query runs super fast, but the one on the live server is over 5 seconds.

My database server is only using around 800MB of the 4GB it has available. here are the related my.ini settings i'm using

default-storage-engine = MYISAM
max_connections = 800
skip-locking
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Disable Federated by default
skip-federated

key_buffer = 512M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

key_buffer = 512M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M