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?
log_queries_not_using_indexes
variable?