JOIN conditions slower than WHERE conditions for field-based queries? [#2723605]

We're having a problem on one server that moved from hosting provider A to Reputable Hosting Provider B. Ever since the move the site has been, well, dog slow. I investigated the queries and have identified that queries which have strings as JOIN arguments perform chronically slow, versus the arguments being passed as WHERE conditions. Here's an example query:

SELECT node.title AS node_title, node.nid AS nid, field_data_field_publication_date.field_publication_date_value AS field_data_field_publication_date_field_publication_date_value, 'node' AS field_data_field_publication_date_node_entity_type, 'node' AS field_data_field_news_source_node_entity_type
FROM 
node node
LEFT JOIN field_revision_field_publication_date field_data_field_publication_date ON node.vid = field_data_field_publication_date.revision_id AND (field_data_field_publication_date.entity_type = 'node' AND field_data_field_publication_date.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('blog')) ))
ORDER BY field_data_field_publication_date_field_publication_date_value DESC
LIMIT 2 OFFSET 0

That query takes at least 6s to run on the production server (VPS, MariaDB 10.1), but only 0.01s locally (MAMP on a fairly beefy MacBook Pro, MySQL 5.5).

I rewrote the query as follows:

SELECT node.title AS node_title, node.nid AS nid, field_data_field_publication_date.field_publication_date_value AS field_data_field_publication_date_field_publication_date_value, 'node' AS field_data_field_publication_date_node_entity_type, 'node' AS field_data_field_news_source_node_entity_type
FROM 
node node
LEFT JOIN field_revision_field_publication_date field_data_field_publication_date ON node.vid = field_data_field_publication_date.revision_id
WHERE (( (node.status = '1') AND (node.type IN  ('blog')) )) AND (field_data_field_publication_date.entity_type = 'node' AND field_data_field_publication_date.deleted = '0')
ORDER BY field_data_field_publication_date_field_publication_date_value DESC
LIMIT 2 OFFSET 0

.. and the query takes 0.1s to run. If I rerun the queries they keep returning similar numbers.

So the question is, is there any reason to pass the arguments as a JOIN conditions instead of a WHERE conditions?

Comments

Comment #1

11 May 2016 at 20:26

DamienMcKenna created an issue. See original summary.

Comment #2

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 11 May 2016 at 20:41

Issue summary:

View changes

Comment #3

attiks CreditAttribution: attiks at Attiks commented 11 May 2016 at 20:48

There are probably running on a different version of mysql, it should not really make a difference, and on the join is the most logical place, one thing you can try to do is use explain and see what the output is.

Comment #4

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 11 May 2016 at 20:49

Issue summary:

View changes

Comment #5

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 11 May 2016 at 21:41

The two queries are running on the same server, one is 6s+, the other is 0.1s.

Yes, the EXPLAIN statements show that the first query requires a temporary table, whereas the second does not.

Comment #6

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 11 May 2016 at 22:14

Interestingly I've *dramatically* improved the page load time using this code:

/**
 * Implements hook_views_query_alter().
 */
function mymodule_views_query_alter(&$view, &$query) {
  // Loop over every table definition.
  foreach ($query->table_queue as &$table) {
    // Look for tables with a JOIN statement that have 'extra' definitions.
    if (isset($table['join']) && !empty($table['join']->extra)) {
      // Loop over each 'extra'.
      foreach ($table['join']->extra as $key => $extra) {
        // Nodequeues are not compatible with this query change so bail.
        if ($extra['field'] == 'qid') {
          continue;
        }
        // Arrays are IN. Yes, a bad pun.
        if (is_array($extra['value'])) {
          $extra['operator'] = 'in';
        }
        // The default operator is equality.
        else {
          $extra['operator'] = '=';
        }
        // Prefix the field name with the table name.
        $extra['field'] = $table['join']->table . '.' . $extra['field'];
        // Remove the 'numeric' value, don't care about that.
        unset($extra['numeric']);
        // Add this 'extra' to the WHERE conditions.
        $query->where[1]['conditions'][] = $extra;
        // Remove the 'extra'.
        unset($table['join']->extra[$key]);
        unset($table['join']->definition['extra'][$key]);
      }
    }
  }
}

Comment #7

attiks CreditAttribution: attiks at Attiks commented 11 May 2016 at 22:46

What mysql version is installed on old/new server

Check my.cnf differences

Comment #8

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 11 May 2016 at 22:51

It's MariaDB 10.1.1 on the new server, locally I'm running MySQ 5.5.4something in MAMP, according to the hosting company's support staff there are no major differences in configuration. I don't know what the old server had, it has already been decommissioned.

Comment #9

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 12 May 2016 at 01:25

I had the (new) hosting company try installing MySQL on the server instead of MariaDB. I then tested the original query again. It ran in 0.09s. So we're going to swap the production server to MySQL, avoid this problem, and let them look into why MariaDB was so much slower.