| Bug #68255 | Query uses temporary table, but "explain" does not mention temporary table | ||
|---|---|---|---|
| Submitted: | 2 Feb 2013 20:22 | Modified: | 6 Feb 2013 1:55 |
| Reporter: | David Marcus | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.9-rc | OS: | Windows (Vista 32 bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | explain, limit, temporary table | ||
[2 Feb 2013 20:22]
David Marcus
[2 Feb 2013 20:23]
David Marcus
Table data to demonstrate the bug
Attachment: BugData.zip (application/zip, text), 2.16 KiB.
[3 Feb 2013 17:20]
MySQL Verification Team
Seems I cannot repeat it here... can you retest and send exact output from mysql command line client ? (don't any any GUI).
Attachment: bug68255_output.txt (text/plain), 3.86 KiB.
[3 Feb 2013 18:25]
David Marcus
It seems it was the MySQL Query Browser. Sorry. Here is what I get with mysql.exe: mysql> show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Created_tmp_tables | 0 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 19 rows in set (0.00 sec) mysql> select * from History where HistoryPlayer = 59161 and HistoryDate <= '2012-01-01' order by HistoryDate desc l imit 0,1; +--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+ | HistoryEvent | HistoryPlayer | HistoryDate | HistoryReportID | HistoryInitialMean | HistoryInitialStDev | HistoryFinalMean | HistoryFinalStDev | +--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+ | 10868 | 59161 | 2011-12-25 | 59161 | 1419 | 59 | 1405 | 56 | +--------------+---------------+-------------+-----------------+--------------------+---------------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show session status where Variable_name like 'Handler%' or Variable_name = 'Created_tmp_tables'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Created_tmp_tables | 0 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 19 rows in set (0.00 sec) mysql> explain extended select * from History where HistoryPlayer = 59161 and HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1; +----+-------------+---------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+-------+---------------+------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | History | range | PlayerDate | PlayerDate | 7 | NULL | 70 | 100.00 | Using index condition | +----+-------------+---------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)
[5 Feb 2013 9:28]
MySQL Verification Team
David, the query browser and other utils often execute some commands in each connection before running the actual query, i think those are causing the problem. You could verify this by enabling general query log. Another cause could be using a different character set by default (which might cause table scan). Therefore I think this is not a bug.
[6 Feb 2013 1:55]
David Marcus
Yes. Sorry. In my previous message I was trying to agree with you that it is not a bug. I already looked in the general query log: See http://forums.mysql.com/read.php?115,577795,578405#msg-578405 By a "different character set", do you mean the character set of the connection is different from that of the table?
