How to investigate and optimise a slow MySQL query?

Question

I have a drupal 7 site running on MySQL. Some pages on the site are excruciatingly slow to load.

I investigated load times and have identified the culprit query, which is regularly taking 10s to execute on some pages. In one case it even took 70s!

The query is from a "view" that generates a short list of related content from elsewhere in the site based on the site taxonomy.

This is an example (with arguments) from one slow page:

SELECT node.nid AS nid, node.title AS node_title, node.created AS node_created, 'podcasts:panel_pane_3' AS view_name, RAND() AS random_field 
FROM node node 
LEFT JOIN (SELECT td.*, tn.nid AS nid 
    FROM taxonomy_term_data td 
    LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid 
    LEFT JOIN taxonomy_index tn ON tn.tid = td.tid 
    WHERE (tv.machine_name IN ('listen')) ) taxonomy_term_data_node 
ON node.nid = taxonomy_term_data_node.nid 
LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid 
WHERE (( (taxonomy_index.tid IN ('472', '350', '742', '681', '3907', '1541', '411', '636', '990', '7757', '680', '743', '11479', '8106', '566', '2230', '11480', '766')) 
  AND (node.nid != '191314' OR node.nid IS NULL) ) 
  AND(( (node.status = '1') 
  AND (node.type IN ('article', 'experiment', 'interview', 'podcast', 'question')) ))) 
ORDER BY random_field ASC, node_created DESC 
LIMIT 5 OFFSET 0

From initial research I thought it would be a case of adding indices, but the columns of the tables concerned seem to have existing index entries.

I'm therefore uncertain how to proceed and would really value some guidance if anyone can help me please?

PS - I did ask MySQL to Explain itself and this is what was generated:

Answer 1

Few recommendations to optimize this query:

1. Avoid selecting unnecessary columns: do you really need all the columns in td.*? In most cases it means that too much information is passed over the network to the application.
2. Mixed ORDER BY directions: you're sorting by two columns: random_field ASC, node_created DESC. Sorting by different orders will prevent index usage, which will slow down the search. Do you think it makes sense to make both ASC or both DESC?
3. I assume that taxonomy_index.tid is numeric, and so are 'node.nid' and 'node.status'. In that case, when comparing them to constants, do not add quotes around the constant, as it will cause an unrequired cast which might prevent index use. For example, turn node.status = '1' to node.status = 1.
4. You are left joining a subquery (taxonomy_term_data_node) - if you're using MySQL < 5.6, or maybe even MySQL 5.7, it's most likely that MySQL can't index that subquery properly. Therefore, I would recommend to extract that subquery to a temporary table, index it and join to it from the outer query. See the transformation below.

So to apply most changes (the ones that do not require your decision, such as part 1 and 2 above), perform these steps:

First, index the main query by adding these indexes:

ALTER TABLE
  `node`
ADD
  INDEX `node_idx_status_nid_title_created` (`status`, `nid`, `title`, `created`);

ALTER TABLE
  `taxonomy_index`
ADD
  INDEX `taxonomy_index_idx_nid` (`nid`);

ALTER TABLE
  `taxonomy_index`
ADD
  INDEX `taxonomy_index_idx_tid_nid` (`tid`, `nid`);

ALTER TABLE
  `taxonomy_term_data`
ADD
  INDEX `taxonomy_term_data_idx_vid_tid` (`vid`, `tid`);

ALTER TABLE
  `taxonomy_vocabulary`
ADD
  INDEX `taxonomy_vocabulary_idx_vid` (`vid`);

First, create the temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        taxonomy_term_data.*,
        tn.nid AS nid 
    FROM
        taxonomy_term_data td 
    LEFT JOIN
        taxonomy_vocabulary tv 
            ON td.vid = tv.vid 
    LEFT JOIN
        taxonomy_index tn 
            ON tn.tid = td.tid 
    WHERE
        (
            tv.machine_name IN (
                'listen'
            )
        );

Now index the subquery: ALTER TABLE temp1 ADD INDEX temp1_idx_nid (nid);

And the outer query will join to it:

SELECT
        node.nid AS nid,
        node.title AS node_title,
        node.created AS node_created,
        'podcasts:panel_pane_3' AS view_name,
        RAND() AS random_field 
    FROM
        node node 
    LEFT JOIN
        temp1 taxonomy_term_data_node 
            ON node.nid = taxonomy_term_data_node.nid 
    LEFT JOIN
        taxonomy_index taxonomy_index 
            ON node.nid = taxonomy_index.nid 
    WHERE
        (
            (
                (
                    taxonomy_index.tid IN (
                        '472', '350', '742', '681', '3907', '1541', '411', '636', '990', '7757', '680', '743', '11479', '8106', '566', '2230', '11480', '766'
                    )
                ) 
                AND (
                    node.nid != '191314' 
                    OR node.nid IS NULL
                )
            ) 
            AND (
                (
                    (
                        node.status = '1'
                    ) 
                    AND (
                        node.type IN (
                            'article', 'experiment', 'interview', 'podcast', 'question'
                        )
                    )
                )
            )
        ) 
    ORDER BY
        random_field ASC,
        node_created DESC LIMIT 5

Answer 2

Thanks for the guidance above, everyone. However, I have solved this with the help of Andy Batey at Cambridge University.

The clue was comparing the EXPLAIN statements generated when the query above was run on MySQL v5.5 (very fast results) versus v5.7 (very slow results); they query was being handled quite differently on the two platforms.

The key was adding this to my.cnf:

optimizer_switch='derived_merge=off'

Now the native query executes in 50ms or less, compared with 12s or longer before.

I hope this helps anyone else who runs into this upgrade problem.