Slow MySQL query not used by fileort

Question

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

tablename = tweets_cache rows = 572,327

this is the query I'm using now, slow, more than 5 seconds.

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

If I select either WHERE or ORDER BY, then the query will be very fast 0.016 seconds.

I have the following indexes in the tweets_cache table.

 PRIMARY published mp category province author 

So I'm not sure why it doesn't use indexes, since mp, provice and published all have indexes? Running a query profile shows that it does not use an index to sort the query and uses filesort, which is very slow.

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

I tried adding a new multie-colum index with "profile and mp". The explanation shows that this new index is indicated under “possible keys” and “key”, but the request time remains unchanged, another 5 seconds.

Here is a screenshot of the profiler information on request.

Something strange, I dumped my database to test on my local desktop so that I would not ruin the site. The same request on my local starts is very fast, milliseconds. So I copied all the same mysql startup variables from the server to my local one, to make sure there were no settings that could cause this. But even after that, the local request is very fast, but the one located on the real server is more than 5 seconds.

My database server uses only 800 MB of the 4 GB it has. here are the related my.ini options that I use

default-storage-engine = MYISAM
max_connections = 800
skip blocking
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 the number of processors * 2 for thread_concurrency
thread_concurrency = 8
# Disable default federation
pass federal

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 indexes do not use it, since mp, provice and published all have indexes?

MySQL will use only one index per table. If you want to do WHERE and ORDER BY in the same step, create a composite index containing matching conditions on the left, with order conditions on the right, for example. in this case (province, mp, published) .

About Optimization ORDER BY .

Answer 2

Try splitting the query into two parts, so that both indexes can work, for example:

 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;