MySQL optimization query

Question

i have one MySQL issue. I have to optimize some queries on my website. One of them i have already done, but there are still some which i cannot resolve without your help.

I have a table called "news":

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(10) NOT NULL auto_increment,
  `edited` smallint(1) NOT NULL default '0',
  `site` varchar(30) default NULL,
  `foreign_id` varchar(25) default NULL,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `image` varchar(255) default NULL,
  `horizontal` smallint(1) NOT NULL,
  `image_author` varchar(255) default NULL,
  `text_author` varchar(255) default NULL,
  `lang` varchar(3) NOT NULL,
  `link` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `redirect` smallint(1) NOT NULL,
  `parent` int(10) NOT NULL,
  `views` int(5) NOT NULL,
  `status` smallint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lang` (`lang`,`status`),
  KEY `date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=47122 ;

as you can see i have two indexes: "lang" and "date"

I have tried some combinations of different indexes and this one has produced me the best results ... unfortunately only on my local computer. On the server i still have bad results. I want to say that the database is the same.

query:

SELECT id FROM news WHERE lang =  'en' AND STATUS =1 ORDER BY DATE DESC LIMIT 0, 10

localhost explain:

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows |    Extra    |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | news  | index | lang          | date |       3 | NULL |   23 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

server explain:

+----+-------------+-------+------+---------------+--------+---------+-------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys |  key   | key_len |     ref     | rows  |            Extra            |
+----+-------------+-------+------+---------------+--------+---------+-------------+-------+-----------------------------+
|  1 | SIMPLE      | news  | ref  | status        | status |      13 | const,const | 15840 | Using where; Using filesort |
+----+-------------+-------+------+---------------+--------+---------+-------------+-------+-----------------------------+

I have looked a lot of other similar topics, but unfortunately i cannot find any solution to work on my server. I will be very glad to here from you some solution with some explanation for that so i can optimize my other queries.

Thanks !

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-06-23T13:11:22

This is your query:

SELECT id
FROM news
WHERE lang = 'en' AND STATUS =1
ORDER BY DATE DESC
LIMIT 0, 10

The best index is one that contains all the fields used in the query (four fields in all). The ordering in the index is by equality conditions in the where clause followed by the order by clause followed by other columns in the select clause.

So, try this index: ndws(leng, status, date, id).

MySQL optimization query

1 Answers