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 | 
+----+-------------+-------+------+-----------------------------------------------------+-----------+---------+-------+-------+-----------------------------+

At first, I tried adding some new indexes to the node table. This worked for some of the queries, but then was detrimental to others.

Finally, I discovered a feature of MySQL that I'd never used before... Index Hints.

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

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 | 
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+

By telling MySQL, in the query itself, which index to prefer in it's execution plan, the query ran 50+ times faster.

After deciding that using Index Hints was the way to go, I had to figure out how to inject a hint into a Views query.

I ended up writing a module specifically for this purpose.

The Views Index Hint module allows an administrator to add an index hint 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.

I've created a project page for the module and a repository of the code up on GitHub.com. This module solves a very rare problem, so unless I get several requests, I won't go to the trouble of adding the module to Drupal.org.

http://nathan.rambeck.org/projects/views-index-hint-module http://github.com/nrambeck/Drupal-Views-Index-Hint

Comments

Identify columns already Index

Hye,

Nice module and I already download and installed it in my site. But, how is the easier way to find what columns already being index rather than look up at phpmyadmin and every table?

No Need

This module should only be used if you are having specific performance problems with specific tables. There is no need to find out what columns are currently indexed for all the tables on your site. Just look at the ones that are having problems.

Either way, I don't know of a way to show all index on all tables in a single command.

Used phpMyAdmin

Thanks for comment.

By the way, I look at through phpMyAdmin to know what have been index. But, using Use Index really improve my site.

Queries spend also reduce around 30% by using this.

Great work!

I'd love to see this module contrib'ed back to D.o. We've just run into a case where a views generated query behaves differently across different database servers, and this module was the fix.

Thanks for the excellent work!

One more thing..

It'd be nice to override the index hint on a per-display basis.. as it sits presently one can only define an index hint for the entire view. Any thoughts?

I'm glad you found the module

I'm glad you found the module helpful. It's been a while since I've used this one, so I'll have to add it to one of my current projects to see what would be required to get overrides working on a per-display basis.

display changes

I was able to figure out how to get different displays to have their own Hints. I've pushed the latest changes to github.com.

I'm a bit hesitant to put this on d.o. primarily because it currently poses a database injection security risk. I've added some validation, to the index hint field, but I've commented out b/c I'm not quite sure the best way to do it.

If you have thoughts on this feel free to let me know.