0

I'm troubleshooting a site with a view with exposed filters whose query is taking a long time to execute when multiple filter options are selected. While the query is running, the CPU load ramps up considerably. We're seeing multiple entries in the MySQL slow log like the following:

(Note: recipe_type, allergy_type, and diet are all term reference fields)

SELECT node.title AS node_title, node.nid AS nid
FROM 
node node
INNER JOIN field_data_field_recipe_type field_data_field_recipe_type ON node.nid = field_data_field_recipe_type.entity_id AND (field_data_field_recipe_type.entity_type = 'node' AND field_data_field_recipe_type.deleted = '0')
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_0 ON node.nid = field_data_field_allergy_type_value_0.entity_id AND field_data_field_allergy_type_value_0.field_allergy_type_tid = '4'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_1 ON node.nid = field_data_field_allergy_type_value_1.entity_id AND field_data_field_allergy_type_value_1.field_allergy_type_tid = '6'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_2 ON node.nid = field_data_field_allergy_type_value_2.entity_id AND field_data_field_allergy_type_value_2.field_allergy_type_tid = '3'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_3 ON node.nid = field_data_field_allergy_type_value_3.entity_id AND field_data_field_allergy_type_value_3.field_allergy_type_tid = '1'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_4 ON node.nid = field_data_field_allergy_type_value_4.entity_id AND field_data_field_allergy_type_value_4.field_allergy_type_tid = '98'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_5 ON node.nid = field_data_field_allergy_type_value_5.entity_id AND field_data_field_allergy_type_value_5.field_allergy_type_tid = '156'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_6 ON node.nid = field_data_field_allergy_type_value_6.entity_id AND field_data_field_allergy_type_value_6.field_allergy_type_tid = '137'
INNER JOIN field_data_field_allergy_type field_data_field_allergy_type_value_7 ON node.nid = field_data_field_allergy_type_value_7.entity_id AND field_data_field_allergy_type_value_7.field_allergy_type_tid = '109'
WHERE (( (node.type IN  ('recipe')) )AND(( (field_data_field_recipe_type.field_recipe_type_tid = '21') AND( (field_data_field_allergy_type_value_0.field_allergy_type_tid = '4') AND (field_data_field_allergy_type_value_1.field_allergy_type_tid = '6') AND (field_data_field_allergy_type_value_2.field_allergy_type_tid = '3') AND (field_data_field_allergy_type_value_3.field_allergy_type_tid = '1') AND (field_data_field_allergy_type_value_4.field_allergy_type_tid = '98') AND (field_data_field_allergy_type_value_5.field_allergy_type_tid = '156') AND (field_data_field_allergy_type_value_6.field_allergy_type_tid = '137') AND (field_data_field_allergy_type_value_7.field_allergy_type_tid = '109') ))))
ORDER BY node_title ASC
LIMIT 10 OFFSET 20;

I verified this particular views-produced query takes ~3 seconds when executed via the mysql command prompt (similar queries with more options selected went as high as 450 seconds). This despite there being only ~900 recipe types, and no more than 5,000 records in any of the term reference tables.

Here is the result of running explain on the above query:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_6
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: field_allergy_type_tid
      key_len: 5
          ref: const
         rows: 561
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_recipe_type
         type: ref
possible_keys: PRIMARY,entity_type,deleted,entity_id,field_recipe_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_6.entity_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_0
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_recipe_type.entity_id
         rows: 23
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_7
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_6.entity_id
         rows: 23
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_5
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_0.entity_id
         rows: 23
        Extra: Using where
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_1
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_recipe_type.entity_id
         rows: 23
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_4
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_5.entity_id
         rows: 23
        Extra: Using where
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_3
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_recipe_type.entity_id
         rows: 23
        Extra: Using where
*************************** 9. row ***************************
           id: 1
  select_type: SIMPLE
        table: field_data_field_allergy_type_value_2
         type: ref
possible_keys: entity_id,field_allergy_type_tid
          key: entity_id
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_3.entity_id
         rows: 23
        Extra: Using where
*************************** 10. row ***************************
           id: 1
  select_type: SIMPLE
        table: node
         type: eq_ref
possible_keys: PRIMARY,node_type
          key: PRIMARY
      key_len: 4
          ref: test_drupal7.field_data_field_allergy_type_value_7.entity_id
         rows: 1
        Extra: Using where
10 rows in set (2.85 sec)

Although views is producing a gnarly query here, I'm surprised it's taking so long with such a small amount of data. I've written a query that yields the same results in 0.02 seconds. I'm beginning to think that MySQL isn't being given enough resources to work with here; I would appreciate any ideas!

(The site uses a mix of MyISAM and InnoDb tables. I tried converting all tables involved in this query to MyISAM, and it still takes a similar amount of time).

  • Actually InnoDB is better at multiple joins than MyISAM in my experience. And proper indexes are a must. What MySQL version are you using, anyway? By the way, if you see a way to write better query for the same result, or blame Drupal for bad indexes, OK. But if you think Drupal did an OK job with schema and query generated is good enough, this is a question for stackoverflow, tag mysql. – Mołot Jun 13 '13 at 22:33
  • I'm not blaming Drupal for bad indexes; I just wanted to point out if that if I re-write the query to be more efficient, it only takes a few microseconds, which could be indicative of mysql running out of resources executing the original query. – mesch Jun 14 '13 at 13:46
  • Re: innodb vs myisam, this query was also generating a problematic pager query which the following module has solved: drupal.org/project/views_litepager It is odd though that MySQL is having such trouble with such a small dataset, leading us to believe perhaps there is a configuration error somewhere. – mesch Jun 14 '13 at 13:49
  • As per Molot's suggestion, I just posted to stackoverflow as well: stackoverflow.com/questions/17111236/… – mesch Jun 14 '13 at 14:50

Your Answer

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

Browse other questions tagged or ask your own question.