Finding optimal index for this MySQL query

Question

In MySQL slow query log I have the following query:

SELECT * FROM `news_items`
WHERE `ctime` > 1465013901 AND `feed_id` IN (1, 2, 9) AND
`moderated` = '1' AND `visibility` = '1'
ORDER BY `views` DESC
LIMIT 5;

Here is the result of EXPLAIN:

+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys                                                                         | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | news_items | index | feed_id,ctime,ctime_2,feed_id_2,moderated,visibility,feed_id_3,cday_complex,feed_id_4 | views | 4       | NULL |    5 | Using where |
+----+-------------+------------+-------+---------------------------------------------------------------------------------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

When I run this query manually, it takes like 0.00 sec but for some reason it appears in MySQL's slow log taking 1-5 seconds sometimes. I believe it happens when server is under high load.

Here is the table structure:

CREATE TABLE IF NOT EXISTS `news_items` (
  `item_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  `source_id` int(10) NOT NULL,
  `feed_id` int(10) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `announce` varchar(255) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8 NOT NULL,
  `hyperlink` varchar(255) CHARACTER SET utf8 NOT NULL,
  `ctime` varchar(11) CHARACTER SET utf8 NOT NULL,
  `cday` tinyint(2) NOT NULL,
  `img` varchar(100) CHARACTER SET utf8 NOT NULL,
  `video` text CHARACTER SET utf8 NOT NULL,
  `gallery` text CHARACTER SET utf8 NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `visibility` enum('1','0') CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `pin` tinyint(1) NOT NULL,
  `pin_dttm` varchar(10) CHARACTER SET utf8 NOT NULL,
  `moderated` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The index named as "views" consists of 1 field only -- views. I also have many other indexes consisting of (for example):

feed_id + views + visibility + moderated
moderated + visibility + feed_id + ctime
moderated + visibility + feed_id + views + ctime

I used fields in mentioned order because that was the only reason MySQL started to use them. However, I never got "Using where; using index" in EXPLAIN.

Any ideas on how to make EXPLAIN to show me "using index"?

Answer 1

To answer your question: "using index" means that MySQL will use only index to satisfy your query. To do this we will need to create a "covered" index (index which "covers" the query) = index which covers both "where" and "order by/group by" and all fields from "select" However, you are doing "select *" so that will not be practical.

MySQL chooses index on views as you have limit 5 in the query. It does that as 1) index is small 2) it can avoid filesort in this case.

I believe the problem is not with the index but rather than with the engine=MyISAM. MyISAM uses table level lock, so if you change the news_items it will be locked. I would suggest converting table to InnoDB.

Another possibility may be that if the table is large, index on (views) may not be the best option.

If you use Percona Server you can enable slow log verbosity option and see the query plan for the slow query as described here: https://www.percona.com/doc/percona-server/5.5/diagnostics/slow_extended_55.html