2

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary?

http://sqlfiddle.com/#!8/f61a2/3/0

CREATE TABLE `test` (
  `a` varchar(45) NOT NULL,
  `b` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `index1` (`b`,`date`),
  KEY `index2` (`b`,`date`,`a`)
) ENGINE=InnoDB;

INSERT INTO test (a, b, date) VALUES ('a1', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a1', 'b2', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b2', now());

INSERT INTO test (a, b, date) VALUES ('a3', 'b1', '2000-01-01 01:01:01');
INSERT INTO test (a, b, date) VALUES ('a3', 'b2', '2000-01-01 01:01:01');
EXPLAIN SELECT
DISTINCT a
FROM test
WHERE b IN ('b1', 'b2') AND
date > NOW() - INTERVAL 1 MONTH;
5

Do you have performance issues because this sounds like premature optimization?

The only good single query what uses indexes is an UNION (which will also filter out duplicates).

If you run EXPLAIN on this query, using temporary won't pop up in the explain..

SELECT
 a
FROM test
WHERE b = 'b1' AND
date > NOW() - INTERVAL 1 MONTH

UNION

SELECT
 a
FROM test
WHERE b = 'b2' AND
date > NOW() - INTERVAL 1 MONTH
;

Edited 2019-09-01

Demo is not valid anymore as sqlfiddle does not run the old MySQL version anymore and because MySQL source code was updated...

I would suggest looking into db-fiddle and switch between MySQL versions 5.5/5.6/5.7, then the problem should be visible between 5.5 and 5.6

By looking into source code file sql/union.cc (C++ code) of MySQL version 5.7.2 m12 (most likely (all) lower versions also using this code, but not sure about that), we can see how UNION works in MySQL. You will discover that the EXPLAIN output sometimes tells you lies, because the source code (in one of the input parameters) indicates that:

is_union_distinct if set, the temporary table will eliminate duplicates on insert

This makes sense because duplicated records are filtered out. Source code snapshot is below:

/*
  Create a temporary table to store the result of select_union.

  SYNOPSIS
    select_union::create_result_table()
      thd                thread handle
      column_types       a list of items used to define columns of the
                         temporary table
      is_union_distinct  if set, the temporary table will eliminate
                         duplicates on insert
      options            create options
      table_alias        name of the temporary table
      bit_fields_as_long convert bit fields to ulonglong

  DESCRIPTION
    Create a temporary table that is used to store the result of a UNION,
    derived table, or a materialized cursor.

  RETURN VALUE
    0                    The table has been created successfully.
    1                    create_tmp_table failed.
*/

bool
select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                  bool is_union_distinct, ulonglong options,
                                  const char *table_alias,
                                  bool bit_fields_as_long, bool create_table)
{
  DBUG_ASSERT(table == 0);
  tmp_table_param.init();
  count_field_types(thd_arg->lex->current_select(), &tmp_table_param,
                    *column_types, false, true);
  tmp_table_param.skip_create_table= !create_table;
  tmp_table_param.bit_fields_as_long= bit_fields_as_long;

  if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                 (ORDER*) 0, is_union_distinct, 1,
                                 options, HA_POS_ERROR, (char*) table_alias)))
    return TRUE;
  if (create_table)
  {
    table->file->extra(HA_EXTRA_WRITE_CACHE);
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
  }
  return FALSE;

}

There is a function that is called create_tmp_table, and thus MySQL is always creating a temporary table when the function create_result_table is called

  • 3
    +1 for you will discover that the EXPLAIN output sometimes tells you lies, the greatest blanket statement in the MySQL Universe. You should get another +1 for the splitting of the query to coerce better query behavior. – RolandoMySQLDBA Nov 30 '13 at 1:55
  • 2
    @RolandoMySQLDBA i was back here because of a other answer which rememberd me posting this.. In short short the explain also does not show when a "implicit cast from date to datetime needed" and needs to look into more records then when you are using the correct datatype.. But it bit to mean offtopic here it seams that Oracle devs have found this answer and fixed it as it seams the explain gives the correct information "now" also check it for 5.7 and 8.. – Raymond Nijland Sep 1 at 15:40
1

Looks like its the datatype casting issue. The below query does not need an temp table

EXPLAIN extended 
SELECT
DISTINCT a
FROM test
WHERE b in ( 'b1', 'b2') AND
date > CURRENT_DATE
  • You forgot the - INTERVAL 1 MONTH. If you add that to your query there is no difference whatsoever. – a_horse_with_no_name Dec 1 '13 at 10:46
  • I tried to interval, bt nothing worked. I could not identify the expected format. Even modifying the table field to datetime did not help – georgecj11 Dec 1 '13 at 11:23
  • @cjg the problem isnt an datatype casting issue the problem is that MySQL is using the index 'index1' this means that the data is ordered like b->date ... because you want distinct values of a what cant be satisfied from the index1 an temporary table is build to keep the results. – Raymond Nijland Dec 2 '13 at 20:56
  • OK, But how is it using the index when I am giving date > CURRENT_DATE? – georgecj11 Dec 3 '13 at 2:44
  • 1
    @cjg and now the DISTINCT a can be satisfied from the mostleft part off the PRIMARY KEY (a, b) so this avoids the use off the temporary table.. but this still wont scale on an large dataset because all index pages (index data) needs to be fetched and an index page size in the InnoDB engine is 16Kb.. – Raymond Nijland Dec 3 '13 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.