Slow MySQL Query not using filesort

Question

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.

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

MySQL 5.0.67

CREATE TABLE `tweets_cache` (                                                             
            `id` bigint(11) unsigned NOT NULL default '0',                                          
            `published` int(11) NOT NULL default '0',                                               
            `title` varchar(140) NOT NULL,                                                          
            `category` varchar(50) NOT NULL,                                                        
            `type` varchar(30) NOT NULL,                                                            
            `author` varchar(25) NOT NULL,                                                          
            `author_full` varchar(150) NOT NULL,                                                    
            `hash` varchar(50) NOT NULL,                                                            
            `lastupdate` int(11) NOT NULL default '0',                                              
            `avatar` varchar(120) NOT NULL,                                                         
            `mp` int(1) NOT NULL default '0',                                                       
            `followers` int(10) NOT NULL default '0',                                               
            `province` varchar(2) NOT NULL,                                                         
            `talkback` varchar(15) NOT NULL default '',                                             
            `in_reply_to_status_id` bigint(11) unsigned NOT NULL default '0',                       
            `author_id` int(11) NOT NULL default '0',                                               
            `tracked` tinyint(1) NOT NULL default '0',                                              
            `geo` varchar(25) NOT NULL default '',                                                  
            PRIMARY KEY  (`id`),                                                                    
            KEY `published` (`published`),                                                          
            KEY `mp` (`mp`),                                                                        
            KEY `category` (`category`),                                                            
            KEY `province` (`province`),                                                            
            KEY `author` USING BTREE (`author`),                                                    
            KEY `home` (`province`,`mp`,`published`)                                                
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 275456 kB'

Answer 1

i'm not sure why its not using the indexes since mp, provice and published all have indexes?

MySQL will only use one index across the table. If you want to do a WHERE and an ORDER BY in the same step, create a compound index containing the matching conditions on the left with the ordering conditions on the right, eg. in this case (province, mp, published).

About ORDER BY optimisation.

Answer 2

Try splitting the query on two pieces, so both indexes can work, something like:

CREATE TEMPORARY TABLE cache
SELECT -describefields- FROM tweets_cache t WHERE t.province='' AND t.mp='0';

SELECT * FROM cache c ORDER BY c.published DESC LIMIT 50;