How can I improve query performances using Using temporary; Using filesort

Question

I am very new to sql and query optimization, I am sorry if its inappropriate to ask such as may be asked previously. But I need help and some thoughts where I can look and start ...

EXPLAIN 
SELECT DISTINCT node.nid AS nid, 
                node.title AS node_title,  
                votingapi_cache_node_points_VotingNumber_sum.value AS votingapi_cache_node_points_votingnumber_sum_value,  
                node.created AS node_created,  
                votingapi_cache_node_hot_VotingNumber_reddit.value AS votingapi_cache_node_hot_votingnumber_reddit_value,  
                'node' AS field_data_field_url_node_entity_type,  
                'node' AS field_data_field_images_node_entity_type,  
                'node' AS field_data_title_field_node_entity_type,  
                'node' AS field_data_field_topic2_node_entity_type
FROM node node
LEFT JOIN votingapi_cache votingapi_cache_node_hot_VotingNumber_reddit 
    ON  node.nid = votingapi_cache_node_hot_VotingNumber_reddit.entity_id  
    AND (votingapi_cache_node_hot_VotingNumber_reddit.entity_type = 'node'  
    AND votingapi_cache_node_hot_VotingNumber_reddit.value_type = 'hot'  
    AND votingapi_cache_node_hot_VotingNumber_reddit.tag = 'VotingNumber'  
    AND votingapi_cache_node_hot_VotingNumber_reddit.function = 'reddit')
LEFT JOIN votingapi_cache votingapi_cache_node_points_VotingNumber_sum  
    ON  node.nid = votingapi_cache_node_points_VotingNumber_sum.entity_id  
    AND (votingapi_cache_node_points_VotingNumber_sum.entity_type = 'node'  
    AND votingapi_cache_node_points_VotingNumber_sum.value_type = 'points'  
    AND votingapi_cache_node_points_VotingNumber_sum.tag = 'VotingNumber'  
    AND votingapi_cache_node_points_VotingNumber_sum.function = 'sum')
INNER JOIN taxonomy_index taxonomy_index  
    ON  node.nid = taxonomy_index.nid
LEFT JOIN field_data_title_field field_data_title_field  
    ON node.nid = field_data_title_field.entity_id  
    AND (field_data_title_field.entity_type = 'node'  
    AND field_data_title_field.deleted = '0')
WHERE ((    (node.status = '1') 
        AND (node.type IN  ('link')) 
        AND (taxonomy_index.tid = '1') 
        AND (field_data_title_field.language IN  ('sv')) 
        AND (votingapi_cache_node_points_VotingNumber_sum.value >= '1') 
      ))
ORDER BY votingapi_cache_node_hot_votingnumber_reddit_value DESC
LIMIT 10 OFFSET 0

Explain shows like below image ie: using Using temporary; Using filesort etc

Please suggest ! Thanks!

Answer 1

For queries this complex, you may have to think outside the box. In this case, I mean think outside the query itself.

ASPECT #1 : Join Buffers

Please note what the MySQL 5.7 Documentation says about join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

You have four(4) JOIN statements in this query, which means you have four JOIN buffers. There is a lot of nested loop processing going on. You could speed this up by increasing the join_buffer_size. Be careful, though. If the join_buffer_size is 1M, this query requires 4M before the query can even start.

ASPECT #2 : Sort Buffer, Temp Tables

In the comments above, Using temporary; Using filesort was mentioned. You could eliminate this somewhat by by increasing the following values:

• tmp_table_size
• max_heap_table_size
• sort_buffer_size

The idea is to lower the number of times sort tables and temp tables go to disk.

For example, you can set them globally for incoming connections:

SET SESSION tmp_table_size = 1024 * 1024 * 64;
SET SESSION max_heap_table_size = 1024 * 1024 * 64;
SET SESSION sort_buffer_size = 1024 * 1024 * 64;

Then, try the EXPLAIN plan and run the query. Hopefully, it will work.

Once you know it worked for you, you can set them globally for incoming connections:

SET GLOBAL tmp_table_size = 1024 * 1024 * 64;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 64;
SET GLOBAL sort_buffer_size = 1024 * 1024 * 64;

Then, go back to my.cnf and add them.

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 64M

Give it a Try and let us know !!!

Answer 2

Rewrite.

Note that you are left joining twice to the same table and the only purpose seems to be getting a couple of different rows for value. Instead, LEFT JOIN to the table once, using the common columns. Then change each of the two rows like

   votingapi_cache_node_points_VotingNumber_sum.value,

to

   CASE ... WHEN ... value ELSE END

Meanwhile, please use a one-letter alias, not this extreme clutter: votingapi_cache_node_points_VotingNumber_sum

There seem to be about 3K rows in taxonomy_index? And most of them are tid=1? If not, then add INDEX(tid). The query probably must do a full table scan of that table.

It would help to see SHOW CREATE TABLE.

votingapi_cache needs INDEX(entity_type, value_type, tag, function) in any order.

For node, add INDEX(status, type).

Do you really want to ORDER BY something that is optional? Will you be seeing NULLs? Maybe LEFT is inappropriate?

First, "Using temporary; Using filesort" is not the end of the world. It is only a small overhead. The complexity of the query is the real villain.

Second, you can't get rid of "Using temporary; Using filesort" without either getting rid of the ORDER BY or by having a single index that handles all of the WHERE, plus the ORDER BY. This is impossible since are multiple tables in the WHERE.

As for the tunables mentioned in another Answer, my general feeling is "you can't tune you way out of a performance problem".