Recently on a Drupal Stackexchange question a contributor asked why his queries were so slow compared to an example query he had tried to re-invent in Views. I looked at his queries and inferred that the slowness was due largely to SQL conditions that were not optimized to use an Index. Bear in mind having been a developer for many years, even on my own Drupal sites I’ve seen this behavior and manually fixed the issues by adjusting Indexes in the database. Today I found a nicer Drupal solution to the problem.
An Overview of the Problem:
For reference the original question can be found at How To Increase Speed of SQL Views Queries?.
The original SQL queries in question where as follows:
Fast Example Query
SELECT SQL_NO_CACHE field_familia_tid, COUNT( DISTINCT node.nid ) AS total FROM field_data_field_oem_ubicacion AS ubi, field_data_field_oem_ref AS oem_ref, field_data_field_familia AS familia, field_data_field_marca AS marca, node WHERE ubi.field_oem_ubicacion_value = oem_ref.entity_id AND oem_ref.entity_id = familia.entity_id AND oem_ref.entity_id = marca.entity_id AND field_marca_tid =69440 AND node.nid = ubi.entity_id GROUP BY field_familia_tid
Slow Views Module Generated Query (1000 times slower)
SELECT taxonomy_term_data_field_data_field_familia.tid AS taxonomy_term_data_field_data_field_familia_tid, COUNT(DISTINCT field_oem_ubicacion_field_collection_item.nid) AS field_oem_ubicacion_field_collection_item_nid FROM {field_collection_item} field_collection_item LEFT JOIN {field_data_field_familia} field_data_field_familia ON field_collection_item.item_id = field_data_field_familia.entity_id AND (field_data_field_familia.entity_type = 'field_collection_item' AND field_data_field_familia.deleted = '0') LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_familia ON field_data_field_familia.field_familia_tid = taxonomy_term_data_field_data_field_familia.tid LEFT JOIN {field_data_field_marca} field_data_field_marca ON field_collection_item.item_id = field_data_field_marca.entity_id AND (field_data_field_marca.entity_type = 'field_collection_item' AND field_data_field_marca.deleted = '0') LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_marca ON field_data_field_marca.field_marca_tid = taxonomy_term_data_field_data_field_marca.tid LEFT JOIN {field_data_field_oem_ubicacion} field_data_field_oem_ubicacion ON field_collection_item.item_id = field_data_field_oem_ubicacion.field_oem_ubicacion_value LEFT JOIN {node} field_oem_ubicacion_field_collection_item ON field_data_field_oem_ubicacion.entity_id = field_oem_ubicacion_field_collection_item.nid WHERE (( (field_data_field_marca.field_marca_tid = '69440') )) GROUP BY taxonomy_term_data_field_data_field_familia_tid
There are some big differences between these queries. Let me try to illustrate the differences with a textual diff of the queries:
Basically, for me, at a glance I can see that although there are many JOINs in the query a large difference between these queries are the additional ON conditions used within the JOINs. These are additional column scans the database engine must perform to perform the query.
As I explained my reponse to the inquiry there is a common solution to this problem:
- Use your database performance tools such as MySQL’s EXPLAIN keyword to view the performance hueristics of your query.
- Having understood your query in (1) add appropriate Indexes to the necessary columns in your database to optimize the query.
In the past I’ve done steps (1) and (2) above by hand. Recently I found the very useful module Field Index that allows you to perform task (2) from within Drupal.
Using the Field Index Module
Simply install the module as any other module and then visit admin/structure/field-indexes. This page will allow you to manage, create, and export SQL Indexes in a similar fashion to Drupal Fields.

Add Index to Last Name custom Field. In this case we add the index to the value field of the string value.
Lastly, after creating these indexes we can see that they were applied to the website:
And, that they were applied to the underlying database schemas:
As an added benefit these indexes are exportable between Drupal site environments when using Features or the Configuration module. Talk about a life saver!
Looking for quality web hosting? Look no further than Arvixe Web Hosting!