How to increase the speed of SQL Views queries? 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).
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.