Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.0
-
Component/s: None
-
Labels:None
Description
EXPLAIN SELECT a FROM t1 GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort SELECT a FROM t1 GROUP BY a;
SHOW EXPLAIN FOR 2;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary Warnings: Note 1003 SELECT a FROM t1 GROUP BY a
bzr version-info
revision-id: psergey@askmonty.org-20120524182239-gq6jtnnovydnthtl date: 2012-05-24 22:22:39 +0400 build-date: 2012-05-30 00:21:27 +0400 revno: 3405
Reproducible with the standard optimizer_switch as well as with all OFF values.
Reproducible with MyISAM, Aria, InnoDB.
- MTR test case
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16);
INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12, t1 t13;
--connect (con1,localhost,root,,)
let $con_id = `SELECT CONNECTION_ID()`;
EXPLAIN SELECT a FROM t1 GROUP BY a;
send SELECT a FROM t1 GROUP BY a;
--connection default
let $run = 20;
while ($run)
{
--error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
eval SHOW EXPLAIN FOR $con_id;
--dec $run
}
--connection con1
--reap
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
In JOIN::exec(), it passes the if-branch shown above (without taking it);
It gets into
if (need_tmp)
{
...
do_select()
...
}
piece right below; do_select() runs the join, the results are piped into a
temporary table.
Then, we reach this part of the code (denote it [part1]):
if (curr_tmp_table->group)
{ // Already grouped
if (!curr_join->order && !curr_join->no_order && !skip_sort_order)
curr_join->order= curr_join->group_list; /* order by group */
curr_join->group_list= 0;
}
Then we arrive to this call:
curr_join->join_free(); /* Free quick selects */
...
if (curr_join->make_simple_join(this, curr_tmp_table))
DBUG_VOID_RETURN;
Then reach this (denote [part3]):
if (curr_join->group_list || curr_join->order)
{
...
/*
Here we sort rows for ORDER BY/GROUP BY clause, if the optimiser
chose FILESORT to be faster than INDEX SCAN or there is no
suitable index present.
Note, that create_sort_index calls test_if_skip_sort_order and may
finally replace sorting with index scan if there is a LIMIT clause in
the query. XXX: it's never shown in EXPLAIN!
OPTION_FOUND_ROWS supersedes LIMIT and is taken into account.
*/
create_sort_index()
}
And here, create_sort_index() calls test_if_skip_sort_order(), which determines
whether filesort should be used.
The important take-away is: when we are executing the join in [part1], we do
not yet know what decision we will make in [part3].
(Q: in this particular example, the result is obvious? Can we fix that? Asking
it from the other end: can we point to a case where we have code in [part3] make
a non-degenerate decision, and code in [part1] run for some period of time?)
On the other hand... We've got the "Using temporary" part correctly. Now, if
using_temporary == TRUE, then if any ordering is required - then
using_filesort=TRUE
(this is because the server is not capable of using temptable's index for
ORDER BY .. LIMIT.. which is odd, but is so, even for MyISAM temptables.
Field::part_of_sortkey is not set for temptable fields).
to build a reliable mysql-test testcase, one needs:
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';