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:

Diff of the fast and slow sql 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:

1. Use your database performance tools such as MySQL’s EXPLAIN keyword to view the performance hueristics of your query.
2. 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 Taxonomy Term Reference Field (it’s TID value in the DB).

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:

After saving the indexes, you may then delete or export them to configuration code.

And, that they were applied to the underlying database schemas:

Index added to underlying DB (last index at bottom of image).

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!