How to increase the speed of SQL Views queries?

Question

I have a View with two relationships, and a few displayed fields. It has a COUNT (use aggregation). The database contains just about 10.000 rows. It takes 4 seconds to complete. A similar query to the very same tables takes 0.002 seconds, i.e., 2000 times faster! It is supposed that the Views module is smart enough not to get involved in a burocracy of tables that will take forever to load the data, but it happens. How can I solve it?

I put the example. Essentialy, they do the same (I need to take a little extra info, but essentialy to get the list of products and number of products in a category, the SQL is working. See the SQL query in one case, and in the other). And I was just very carefully adding the two relationships and the fields, so either I did something very wrong or Views SQL UI needs a very lot of improvement.

Quite fast 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

A similar query, done carefully with the Views UI. Extremely slow, 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

Note: Afterr careful inspection of SQL and also from suggestion by user tenken, I replaced the LEFT JOIN by WHERE-AND in the SQL. The result was almost identical, and fast, so now the question is: why does Drupal uses by default the LEFT JOIN, and where can I change it (if possible, not programmaticaly, in order to have the logic inside the View).

Answer 1

The primary reason Views uses LEFT JOIN is to account for empty fields. INNER JOIN implicitly assumes there are field values. Depending on what source you read, INNER JOIN can be preferable over the WHERE-AND style because it will limit the amount of data the WHERE operates on.

Your optimized query makes a few assumptions on the data.

1. Your fields are only on the field collections. It is possible for the same field to be on multiple entity types, hence the f.entity_type = ... portion of the query.

2. Your fields still exist. The deleted column is used during garbage collection during cron runs when you delete a field (see field_cron and field_purge_batch).

3. It looks like you have optimized some of the other joins away, by knowing what data you don't need.

A better benchmark would be to convert your query to use INNER JOIN. You can do this with a hook_views_query_alter(&$view, &$query). I don't have a working example of this handy, but dpm() the $query object, and you will see the property that has the joins in it. You can change the join type, and you can potentially remove unnecessary joins.

Answer 2

Take the query from the Views admin and from the mysql command line, or phpmyadmin add the EXPLAIN keyword in front of it. Assure it's using indexes on query criteria and that the query is optimized.

If it's not a fully optimized query then simply add an index to your SQL schema as needed until it's as optimized as it can be.

You can either add the index directly to tables through phpmyadmin, or create a module to index the necessary db fields (and then drop indexes if the module is disabled).

Drupal ships with a db that is not fully optimizted for any query you could generate with views. Module creators make general assumptions in the table they make to provide primary keys for example -- but they don't index every column of their schemas as taht would be wasteful. How you use Fields on your site is different from how I would use fields on my sites ... etc.

1 module that may help you index your Drupal fields (after evaluating the EXPLAIN output is) Field Index:

Field modules don't always add indexes for its columns (value column of text field for example), this leads to poor performance in views when we would like sort or filter views using the unindexed columns. We can check the query executed by views and add custom indexes manually, but it's not easy to deploy.

With field_index module, we can create custom indexes for fields. The indexes are exportable.

Answer 3

In fact, it is the Views config which is wrongly done. To get the same results than the query SQL, use «Require this relationship» in the relations and you are done.