[MDEV-298] SHOW EXPLAIN: Plan returned by SHOW EXPLAIN only contains 'Using temporary' while the standard EXPLAIN says 'Using temporary; Using filesort' Created: 2012-05-29  Updated: 2012-07-27  Resolved: 2012-07-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-165 MWL#182: Explain running statements: ... Closed

 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.

  1. 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



 Comments   
Comment by Sergei Petrunia [ 2012-06-04 ]

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';

Comment by Sergei Petrunia [ 2012-07-19 ]

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?)

Comment by Sergei Petrunia [ 2012-07-19 ]

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).

Generated at Thu Feb 08 06:27:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.