Mysql – Fast query, in theory using index, still in the slow-query log

explainMySQLperformanceslow-logunion

I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows.
What's wrong here? Is my second "improved" version any better?
Any suggestion?

  mysql> # Original
  mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, 1 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships1
      -> LEFT JOIN productsRelationshipsDesc on 1=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 2 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships2
      -> LEFT JOIN productsRelationshipsDesc on 2=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships3
      -> LEFT JOIN productsRelationshipsDesc on 3=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 5 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships5
      -> LEFT JOIN productsRelationshipsDesc on 5=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 6 as relType, relTypeDesc, fracQty, '24794' as source      FROM productsRelationships6
      -> LEFT JOIN productsRelationshipsDesc on 6=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 7 as relType, relTypeDesc, 0 as fracQty, '24794' as source FROM productsRelationships7
      -> LEFT JOIN productsRelationshipsDesc on 7=relTypeID
      -> WHERE relDst='24794' OR relSrc='24794'
      -> ORDER BY relType, relSrc, RelDst;
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  | id | select_type  | table                     | type        | possible_keys       | key           | key_len | ref   | rows  | Extra                                   |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  |  1 | PRIMARY      | productsRelationships1    | index       | PRIMARY,src-1       | src-1         | 2       | NULL  |   663 | Using where; Using index                |
  |  1 | PRIMARY      | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  2 | UNION        | productsRelationships2    | index       | src-dst-2           | src-dst-2     | 4       | NULL  | 13126 | Using where; Using index                |
  |  2 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  3 | UNION        | productsRelationships3    | index       | PRIMARY             | PRIMARY       | 4       | NULL  | 11459 | Using where; Using index                |
  |  3 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  4 | UNION        | productsRelationships5    | index       | PRIMARY,src-5       | src-5         | 2       | NULL  |   369 | Using where; Using index                |
  |  4 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  5 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL  |     2 | Using union(dst-6,PRIMARY); Using where |
  |  5 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  |  6 | UNION        | productsRelationships7    | index       | PRIMARY,src-7       | src-7         | 2       | NULL  |     1 | Using where; Using index                |
  |  6 | UNION        | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | const |     1 | Using index                             |
  | NULL | UNION RESULT | <union1,2,3,4,5,6>        | ALL         | NULL                | NULL          | NULL    | NULL  |  NULL | Using filesort                          |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+-------+-------+-----------------------------------------+
  13 rows in set (0.00 sec)

  mysql>
  mysql>
  mysql> # Improved?
  mysql> EXPLAIN SELECT SQL_NO_CACHE relSrc, relDst, relType, fracQty, source, relTypeDesc FROM (
      -> SELECT relSrc, relDst, 1 as relType, 0 as fracQty, '24794' as source FROM productsRelationships1
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 2 as relType, 0 as fracQty, '24794' as source FROM productsRelationships2
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source FROM productsRelationships3
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 5 as relType, 0 as fracQty, '24794' as source FROM productsRelationships5
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 6 as relType,      fracQty, '24794' as source FROM productsRelationships6
      -> WHERE relDst='24794' OR relSrc='24794'
      -> UNION ALL
      -> SELECT relSrc, relDst, 7 as relType, 0 as fracQty, '24794' as source FROM productsRelationships7
      -> WHERE relDst='24794' OR relSrc='24794'
      -> ) AS rels
      -> LEFT JOIN productsRelationshipsDesc ON relType=relTypeID
      -> ORDER BY relType, relSrc, RelDst;
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  | id | select_type  | table                     | type        | possible_keys       | key           | key_len | ref          | rows  | Extra                                                |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  |  1 | PRIMARY      | <derived2>                | ALL         | NULL                | NULL          | NULL    | NULL         |    38 | Using filesort                                       |
  |  1 | PRIMARY      | productsRelationshipsDesc | ref         | relTypeID           | relTypeID     | 1       | rels.relType |     1 | Using index                                          |
  |  2 | DERIVED      | productsRelationships1    | index       | PRIMARY,src-1       | src-1         | 2       | NULL         |   663 | Using where; Using index                             |
  |  3 | UNION        | productsRelationships2    | index       | src-dst-2           | src-dst-2     | 4       | NULL         | 13126 | Using where; Using index                             |
  |  4 | UNION        | productsRelationships3    | index       | PRIMARY             | PRIMARY       | 4       | NULL         | 11459 | Using where; Using index                             |
  |  5 | UNION        | productsRelationships5    | index       | PRIMARY,src-5       | src-5         | 2       | NULL         |   369 | Using where; Using index                             |
  |  6 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL         |     2 | Using union(dst-6,PRIMARY); Using where; Using index |
  |  7 | UNION        | productsRelationships7    | index       | PRIMARY,src-7       | src-7         | 2       | NULL         |     1 | Using where; Using index                             |
  | NULL | UNION RESULT | <union2,3,4,5,6,7>        | ALL         | NULL                | NULL          | NULL    | NULL         |  NULL |                                                      |
  +----+--------------+---------------------------+-------------+---------------------+---------------+---------+--------------+-------+------------------------------------------------------+
  9 rows in set (0.00 sec)

Those are the tables definitions

  mysql> SHOW CREATE TABLE productsRelationships1\G
  *************************** 1. row ***************************
         Table: productsRelationships1
  Create Table: CREATE TABLE `productsRelationships1` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-1` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships2\G
  *************************** 1. row ***************************
         Table: productsRelationships2
  Create Table: CREATE TABLE `productsRelationships2` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    KEY `src-dst-2` (`relSrc`,`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships3\G
  *************************** 1. row ***************************
         Table: productsRelationships3
  Create Table: CREATE TABLE `productsRelationships3` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships5\G
  *************************** 1. row ***************************
         Table: productsRelationships5
  Create Table: CREATE TABLE `productsRelationships5` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-5` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships6\G
  *************************** 1. row ***************************
         Table: productsRelationships6
  Create Table: CREATE TABLE `productsRelationships6` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relType` tinyint(2) unsigned NOT NULL DEFAULT '6',
    `fracQty` int(2) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-6` (`relSrc`),
    UNIQUE KEY `dst-6` (`relDst`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationships7\G
  *************************** 1. row ***************************
         Table: productsRelationships7
  Create Table: CREATE TABLE `productsRelationships7` (
    `relSrc` smallint(5) unsigned NOT NULL,
    `relDst` smallint(5) unsigned NOT NULL,
    PRIMARY KEY (`relSrc`,`relDst`),
    UNIQUE KEY `src-7` (`relSrc`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

  mysql> SHOW CREATE TABLE productsRelationshipsDesc\G
  *************************** 1. row ***************************
         Table: productsRelationshipsDesc
  Create Table: CREATE TABLE `productsRelationshipsDesc` (
    `relTypeID` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
    `relTypeDesc` varchar(100) NOT NULL,
    UNIQUE KEY `relTypeID` (`relTypeID`,`relTypeDesc`)
  ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)

And this is the amount of data in every table.

  mysql> SELECT COUNT(1) FROM productsRelationships1\G
  *************************** 1. row ***************************
  COUNT(1): 663
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships2\G
  *************************** 1. row ***************************
  COUNT(1): 263
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships3\G
  *************************** 1. row ***************************
  COUNT(1): 8551
  1 row in set (0.01 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships5\G
  *************************** 1. row ***************************
  COUNT(1): 369
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships6\G
  *************************** 1. row ***************************
  COUNT(1): 80
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationships7\G
  *************************** 1. row ***************************
  COUNT(1): 0
  1 row in set (0.00 sec)

  mysql> SELECT COUNT(1) FROM productsRelationshipsDesc\G
  *************************** 1. row ***************************
  COUNT(1): 7
  1 row in set (0.00 sec)

UPDATE:
Acording to the EXPLAIN It APPEARS to be using index but the slow query log says this # Query_time: 0.005458 Lock_time: 0.000340 Rows_sent: 38 Rows_examined: 50579 and this is when I take out the ORDER clause
It may be a fast query, but it's called nearly in every single operation of the system.

UPDATE2:
Ok, I'm going crazy here

# Query_time: 0.003527  Lock_time: 0.000164 Rows_sent: 38  Rows_examined: 8554
SET timestamp=1370017780;
SELECT SQL_NO_CACHE relSrc, relDst, 3 as relType, 0 as fracQty, '24794' as source
FROM   productsRelationships3
WHERE relSrc='24794' OR relDst='24794';

How is this possible?
productsRelationships3 has a composed index PRIMARY KEY (relSrc,relDst) why it
's examining every single row?

Best Answer

There's a lot of good material in the comments, but I overlooked something obvious early on that actually makes the answer to the question fairly straightforward.

What I missed was the repeated use of OR in the queries, which I originally mis-read as AND... and this makes a great deal of difference. You're asking for this:

WHERE relDst='24794' OR relSrc='24794'

It's unfortunate that MySQL's EXPLAIN uses the phrase using index because that does not mean that it is doing lookups in the index. You can actually be doing what amounts to a full table scan and still have Using index if all of the columns you need in a table are contained in a single index. In fact, as I'll show below, that's exactly what's happening here.

Using index means the optimizer has decided to read the data from the index, instead of reading the data from the actual table rows. Since an index is almost always a proper subset of the columns of the table, it follows that if all of the columns in the SELECT can be found in a single index, that this means fewer bytes of data to be handled, potentially reducing the cost of the query in terms of CPU, I/O, and/or memory. So this is a good strategy... but it doesn't mean the index is being used for lookups... just that it's a less-costly place from which to read the necessary column data.

The type column of most of your queries shows index. Once again, this doesn't mean an index is being used. This is actually the same thing as type=ALL -- a full table scan -- but reading from an index tree instead of the table data... so this value in type goes hand in hand with Using index.

The index being used for lookups is what's normally shown in the key column of the EXPLAIN output... but when type=index and ref=NULL then that's not what's happening.

So, in most of your queries, the optimizer has decided to read the entire PRIMARY key index from start to finish, looking for rows a matching value in one column OR the other.

A multi-column index can't be used to resolve OR conditions. It can only be used to resolve AND conditions... or conditions involving a continuous subset of columns that begins with the first column in the index.

A 2-column index is very much like a printed city telephone directory, sorted by last name then first name... where the names are typically printed in bold, and the addresses are printed in a smaller or less prominent typeface.

I can't use a telephone directory to find everybody with last name "Jones" OR first name "John", but I can use it very effectively and efficiently to find everybody with last name "Jones" AND first name "John." (Hence the big difference between AND vs OR that I missed earlier.) I can also use it very effectively to find everybody with last name "Jones" regardless of their first name, but it is completely useless when all I am trying to do is find everybody with first name "John" regardless of their last name.

Or maybe not quite. It's not optimum, but it can still be useful to an extent. Continuing with that analogy, assuming I still needed to find everybody with first name "John," I'll have to read every line in the directory, but I do not need to read the printing that isn't in bold -- the addresses don't tell me anything about first names.

Hence the value of reading directly from the index, even if I can't benefit from the ordering of the data in the index... Using index means the optimizer has found a covering index that can satisfy its need to read column data, whether or not the index is used for lookups or joins.

One table stands out as an exception:

|  5 | UNION        | productsRelationships6    | index_merge | PRIMARY,src-6,dst-6 | dst-6,PRIMARY | 2,2     | NULL  |     2 | Using union(dst-6,PRIMARY); Using where |

The difference here is that you've got indexes on both columns, where the column of interest is the leftmost (in this case, the only) column in the index. 

UNIQUE KEY `src-6` (`relSrc`),
UNIQUE KEY `dst-6` (`relDst`)

The optimizer cleverly chooses the index_merge optimization -- looking up the desired value in both indexes and merging the identified rows.

If all of your tables had a comparable arrangement, you should see a lot fewer rows examined in your slow query log -- and the query might drop out of the slow query log altogether.

These indexes don't necessarily have to be unique indexes, though that's ideal if the data supports it, since a unique index can only ever return one value... but I would suggest adding this to each table:

KEY dst_src (relDst,relSrc)

...or just...

KEY (relDst)

Now, you have two indexes -- the primary key and this new one (again, you may want to make it UNIQUE if that suits your data, or not if it doesn't) ... one of them sorted by relSrc (the primary key) and the other sorted by relDst... so the optimizer may choose to merge the results from this key and the primary key and significantly reduce the number of rows involved.

The filesort can't be avoided, because UNION ALL always creates a temporary table behind the scenes... which can't have an index... but I'm inclined to think, based on the above, that this isn't the reason the query is getting logged as "not using indexes."

Finally, when you're benchmarking, you may want to use SELECT SQL_NO_CACHE in your queries, to be sure that you're always actually executing the query each time and not seeing deceptively small or strangely inconsistent response times because you're getting results from the query cache, if it's enabled on your system.

Related Question