Views Index Hint module

On one of my projects, I cam across a certain performance edge case on a site with a large volume of data. For whatever reason, the MySQL optimizer refused to use the fastest index in a handful of views queries, causing these queries to be painfully slow.

Here is a simplified example of a query generated by views:

SELECT nid, title FROM node WHERE status <> 0 AND type IN ("story") ORDER BY created DESC;

When viewing the explain plan for this query, it shows my that MySQL is choosing to use the node_type index instead of node_created. Because of the large dataset, this causes MySQL to implement a filesort (see Extra column).

mysql> EXPLAIN SELECT nid, title FROM node WHERE status <> 0 AND type IN ("story") ORDER BY created DESC LIMIT 20;
+----+-------------+-------+------+-----------------------------------------------------+-----------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys                                       | key       | key_len | ref   | rows  | Extra                       |
+----+-------------+-------+------+-----------------------------------------------------+-----------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | node  | ref  | node_status_type,node_type,node_status_type_created | node_type | 14      | const | 85666 | Using where; Using filesort | 
+----+-------------+-------+------+-----------------------------------------------------+-----------+---------+-------+-------+-----------------------------+

Using the USE INDEX syntax, you can tell MySQL which index to prefer when it executes the query.

SELECT nid, title FROM node USE INDEX (node_created) WHERE status <> 0 AND type IN ("story") ORDER BY created DESC LIMIT 20;

The explain plan for this query shows a much more efficient execution.

mysql> EXPLAIN SELECT nid, title FROM node USE INDEX (node_created) WHERE status <> 0 AND type IN ("editorial") ORDER BY created DESC LIMIT 20;
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+
|  1 | SIMPLE      | node  | index | NULL          | node_created | 4       | NULL | 171333 | Using where | 
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+

The Views Index Hint module allows an administrator to add an index hint like this to any View through the main View edit interface. Basically it adds a new field in the General section called Index Hint that lets you enter something like "USE INDEX (node_created)" which would be injected into your Views query.

Using an index hint in your views query may not be the right option in every situation, but it provides the flexibility to use this lesser know MySQL feature when absolutely necessary.