2

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).

  • 3
    Why Views uses LEFT JOIN aside, those queries are radically different. Your optimized query makes some very implicit assumptions about the data. I'll try to expand on this later. – mpdonadio Nov 20 '15 at 18:20
4

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.

  • Nice explanation of some of the whys certain conditions are included by Views by default. – tenken Nov 20 '15 at 21:41
  • Very nice explanation. Indeed the problem was with the LEFT. What I don't understand is that there are few of those rows which could be empty. But now the problem would be, in any case, with MySQL perforamnce (100-1000 fold time of difference is too much). The trick of the hook_query_alter for fine tuning or the Require this relationship worked perfectly. – Cesar Nov 23 '15 at 7:14
  • @Cesar It's also possible that you need to optimize your MySQL instance. The default my.cnf files are pretty bad, even the "large server" ones. mysqltuner provides a good baseline to build off of. – mpdonadio Nov 23 '15 at 13:44
  • Thanks, MPD, I will give it a look. I knew that there was less performance with LEFT JOIN instead of INNER or WHERE-AND, but never had found such a huge difference in performace, 100-1000th-fold!! – Cesar Nov 23 '15 at 17:58
5

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.

  • It uses all the indexes. In fact, my query uses the same tables and fields now, and I reduced to a single query, going 1000 times faster. It has to do either with some very wrong problem with Views UI or something I am doing bad, but cannot see what because I am using the less elements possible when making the relationship and the display and the filters. – Cesar Nov 20 '15 at 16:30
  • Try running without DISTINCT and see what the difference is. It's a notorious drag on performance. Do you get duplicate rows without it? – Alfred Armstrong Nov 20 '15 at 16:40
  • @Cesar hmm I still suspect the JOIN on conditions are slowing it down (and that the join on conditions aren't indexed). Otherwise can you show a screenshot of the views execution time from the views admin, and a screenshot of the Explain output for the views query? ... I assume also the query is slower and not the page load time. Your Views output may nor may not allow to be cached in the Views admin setting. – tenken Nov 20 '15 at 17:14
  • I am inspecting the JOIN sentences. Also notice (I have updated code so they show exactly the same results) that the "wrong" SQL is using the taxonomy tables, but that is not a problem - if I need the term name it would be better. I am suspecting that the trick is in the JOIN, I am changing by WHERE and AND in the SQL in phpmyadmin. – Cesar Nov 20 '15 at 17:21
  • It was that!!! Almost exactly the same result in milliseconds. How can I change the JOIN by WHERE-AND in the Views UI ? – Cesar Nov 20 '15 at 17:25
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.