Mysql – Why is this SELECT creating temp tables

MySQLmysql-5.5select

When I run the following SELECT, it takes quite long, typically 30 or 40 minutes (I'm on Debian, MySQL 5.5.62):

SELECT ga.currency, o.organisation_name, os.hostname, g.global_name,
       SUM(IF(ga.type ='WAGER', ga.amount,0)) AS 'total wager lc',
       SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.gbp) AS 'total wager gbp',
       SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.eur) AS 'total wager eur',
       SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.usd) AS 'total wager usd',
       SUM(IF(ga.type = 'WIN', ga.amount, 0)) AS 'total win lc',
       SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.gbp) AS 'total win gbp',
       SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.eur) AS 'total win eur',
       SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.usd) AS 'total win usd',
       CONCAT( DATE(ga.created_timestamp),' ', HOUR(ga.created_timestamp)) AS date_and_hour
 FROM game_action ga
 JOIN user u               ON u.user_id = ga.user_id
 JOIN game g               ON g.game_id = ga.game_id
 JOIN game_instance gi     ON gi.game_instance_id = ga.game_instance_id
 JOIN organisation_site os ON u.organisation_site_id = os.organisation_site_id
 JOIN organisation o       ON o.organisation_id = os.organisation_id
 INNER JOIN reporting.fx_conversion fx ON fx.date = date(ga.created_timestamp) 
                                      AND fx.currency = ga.currency

WHERE ga.created_timestamp BETWEEN str_to_date(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T')
  AND str_to_date(CONCAT(DATE_SUB(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 WEEK)), INTERVAL -1 DAY),' 23:59:59'),'%Y-%m-%d %T')
  AND gi.status IN ('RESOLVED', 'AUTO_COMPLETED')

GROUP BY ga.currency
       , o.organisation_name
       , os.hostname
       , g.global_name
       , date_and_hour

ORDER BY o.organisation_name
       , os.hostname
       , ga.created_timestamp ASC
;

However, the explain suggests that this shouldn't happen (or I don't understand how things work):

mysql> explain extended ...
+----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                         | key     | key_len | ref                            | rows     | filtered | Extra                                        |
+----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+
|  1 | SIMPLE      | ga    | ALL    | GA_IX01,GA_IX02,GA_IX03,game_action_created_timestamp,ga_id_cur_tstmp | NULL    | NULL    | NULL                           | 23851088 |    49.75 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                                                               | PRIMARY | 4       | gameiom.ga.game_id             |        1 |   100.00 |                                              |
|  1 | SIMPLE      | fx    | ref    | PRIMARY                                                               | PRIMARY | 3       | func                           |       22 |   100.00 | Using where                                  |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY,U_UK01,U_IX_04                                                | PRIMARY | 4       | gameiom.ga.user_id             |        1 |   100.00 |                                              |
|  1 | SIMPLE      | os    | eq_ref | PRIMARY,ORGST_UK1_orgid_extid,ORGST_FK01                              | PRIMARY | 4       | gameiom.u.organisation_site_id |        1 |   100.00 |                                              |
|  1 | SIMPLE      | o     | eq_ref | PRIMARY                                                               | PRIMARY | 4       | gameiom.os.organisation_id     |        1 |   100.00 |                                              |
|  1 | SIMPLE      | gi    | ref    | PRIMARY,game_instance_status                                          | PRIMARY | 8       | gameiom.ga.game_instance_id    |        1 |   100.00 | Using where                                  |
+----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `gameiom`.`ga`.`currency` AS `currency`,`gameiom`.`o`.`organisation_name` AS `organisation_name`,`gameiom`.`os`.`hostname` AS `hostname`,`g`.`global_name` AS `global_name`,sum(if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0)) AS `total wager lc`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`gbp`)) AS `total wager gbp`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`eur`)) AS `total wager eur`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`usd`)) AS `total wager usd`,sum(if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0)) AS `total win lc`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`gbp`)) AS `total win gbp`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`eur`)) AS `total win eur`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`usd`)) AS `total win usd`,concat(cast(`gameiom`.`ga`.`created_timestamp` as date),' ',hour(`gameiom`.`ga`.`created_timestamp`)) AS `date_and_hour` from `gameiom`.`game_action` `ga` join `gameiom`.`user` `u` join `gameiom`.`game` `g` join `gameiom`.`game_instance` `gi` join `gameiom`.`organisation_site` `os` join `gameiom`.`organisation` `o` join `reporting`.`fx_conversion` `fx` where ((`gameiom`.`u`.`user_id` = `gameiom`.`ga`.`user_id`) and (`g`.`game_id` = `gameiom`.`ga`.`game_id`) and (`gameiom`.`os`.`organisation_site_id` = `gameiom`.`u`.`organisation_site_id`) and (`gameiom`.`o`.`organisation_id` = `gameiom`.`os`.`organisation_id`) and (`gameiom`.`ga`.`created_timestamp` between <cache>(str_to_date(concat(date_format((now() - interval 1 week),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T')) and <cache>(str_to_date(concat((last_day((now() - interval 1 week)) - interval -(1) day),' 23:59:59'),'%Y-%m-%d %T'))) and (`gameiom`.`gi`.`status` in ('RESOLVED','AUTO_COMPLETED')) and (`reporting`.`fx`.`date` = cast(`gameiom`.`ga`.`created_timestamp` as date)) and (convert(`reporting`.`fx`.`currency` using utf8) = `gameiom`.`ga`.`currency`) and (`gameiom`.`gi`.`game_instance_id` = `gameiom`.`ga`.`game_instance_id`)) group by `gameiom`.`ga`.`currency`,`gameiom`.`o`.`organisation_name`,`gameiom`.`os`.`hostname`,`g`.`global_name`,concat(cast(`gameiom`.`ga`.`created_timestamp` as date),' ',hour(`gameiom`.`ga`.`created_timestamp`)) order by `gameiom`.`o`.`organisation_name`,`gameiom`.`os`.`hostname`,`gameiom`.`ga`.`created_timestamp`
1 row in set (0.00 sec)

Running the SELECT with profiling shows:

mysql> show profile;
+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| starting                       |   0.000013 |
| Waiting for query cache lock   |   0.000003 |
| Waiting on query cache mutex   |   0.000002 |
| checking query cache for query |   0.000147 |
| checking permissions           |   0.000003 |
| checking permissions           |   0.000002 |
| checking permissions           |   0.000002 |
| checking permissions           |   0.000002 |
| checking permissions           |   0.000002 |
| checking permissions           |   0.000002 |
| checking permissions           |   0.000004 |
| Opening tables                 |   0.000046 |
| System lock                    |   0.000065 |
| init                           |   0.000078 |
| optimizing                     |   0.000046 |
| statistics                     |   0.000157 |
| preparing                      |   0.000026 |
| Creating tmp table             |   0.000199 |
| executing                      |   0.000003 |
| Copying to tmp table           | 999.999999 |
| Sorting result                 |   0.034862 |
| Sending data                   |   0.019785 |
| end                            |   0.000005 |
| removing tmp table             |   0.000379 |
| end                            |   0.000005 |
| query end                      |   0.000005 |
| closing tables                 |   0.000080 |
| freeing items                  |   0.000040 |
| logging slow query             |   0.000002 |
| logging slow query             |   0.000111 |
| cleaning up                    |   0.000006 |
+--------------------------------+------------+
31 rows in set (0.00 sec)

Now, I have to admit that I don't understand the warning after the EXPLAIN, and perhaps it refers to something that causes the tmp table (?). However, that aside, is the SQL shaped such that it will lead to tmp tables? What I've found in the docs is a bit hard to follow.

Edit:

Sorr, I had a couple of urgent things to do yesterday, but I've now had time to re-visit this subject. I tried to run explain on my select one more time, and now it looks a lot more like I thought I remembered it:

+----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys                            | key                   | key_len | ref                             | rows | filtered | Extra                           |
+----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | o     | ALL    | PRIMARY                                  | NULL                  | NULL    | NULL                            |    2 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | os    | ref    | PRIMARY,ORGST_UK1_orgid_extid,ORGST_FK01 | ORGST_UK1_orgid_extid | 4       | gameiom.o.organisation_id       |    5 |   100.00 |                                 |
|  1 | SIMPLE      | u     | ref    | PRIMARY,U_UK01,U_IX_04                   | U_IX_04               | 4       | gameiom.os.organisation_site_id | 1566 |   100.00 | Using index                     |
|  1 | SIMPLE      | ga    | ref    | GA_IX01,GA_IX02,GA_IX03                  | GA_IX02               | 4       | gameiom.u.user_id               |  287 |   100.00 | Using where                     |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                                  | PRIMARY               | 4       | gameiom.ga.game_id              |    1 |   100.00 |                                 |
|  1 | SIMPLE      | gi    | ref    | PRIMARY                                  | PRIMARY               | 8       | gameiom.ga.game_instance_id     |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | fx    | ref    | PRIMARY                                  | PRIMARY               | 3       | func                            |    5 |   100.00 | Using where                     |
+----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+
7 rows in set, 1 warning (0.00 sec)

And in fact, I copied the SELECT from what I had uploaded at the beginning of my question. The only explanation I can think of is that I must have copied the wrong output. Sorry about that. The profiling data I uploaded should be right, but I'll re-run just to make sure and update if there's any change.

Best Answer

It is not surprising that temp tables would come into play. Why ???

According to the MySQL 5.5 Documentation on join_buffer_size (Italics mine):

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.

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.

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.5, “Nested-Loop Join Algorithms”.

Given the aforementioned documentation and your profile, the join_buffer_size is most likely too small. This would trigger mysqld to send the JOIN buffer to a temp table. What makes this worse is that you have six(6) JOIN clauses. That means you have six(6) JOIN buffers.

Trying increasing the join_buffer_size but don't go crazy.

SUGGESTION

Try executing the query dynamically with a larger join_buffer_size.

SET @oldvalue = @@session.join_buffer_size;
SET @newvalue = @oldvalue * 2;
SET SESSION join_buffer_size = @newvalue;
<Run Your Query Here>

Check you EXPLAIN plan and profile and see if that makes a difference.

GIVE IT A TRY !!!

UPDATE 2020-01-15 10:34 EDT

Assuming all indexes for the join exist leaving you with only one JOIN buffer, the last JOIN clause

INNER JOIN reporting.fx_conversion fx ON fx.date = date(ga.created_timestamp) 
                                  AND fx.currency = ga.currency

looks rather nasty because you are using the DATE() function.

That has temp table written all over it. Why ??? That JOIN cannot use an index because of the complex expression. Perhaps one or more JOIN buffers are in use.

Related Question