Bad Plans when using ORDER BY in a subquery
24 views
Skip to first unread message
Eric
Nov 12, 2012, 6:36:50 PM11/12/12
to percona-d...@googlegroups.com
All,
I have a query that performs poorly without a FORCE INDEX when using an ORDER BY in a subquery.
I have two tables a and b that are clearly connected/related by application_id.
In the examples below you will see that when I add an "ORDER BY" to my subquery the optimizer chooses the surrogate primary key of the b table which makes the query unacceptably slow.
It seems to be favoring the use of an index for sake of of the "ORDER BY" rather than row selection.
Is this a Bug or just another limitation of the optimizer?
*I would also like to comment that the explain plan output has a bug where it chooses a key that is not in the possible key list.
# With ORDER BY in subquery.
SELECT SQL_NO_CACHE a.application_id, (SELECT b.promo_id FROM b WHERE b.application_id = a.application_id ORDER BY b.campaign_info_id LIMIT 1) AS promo_id FROM a
WHERE Application_id > 1533456895 LIMIT 1 ;
+----------------+----------+
| application_id | promo_id |
+----------------+----------+
| 1533456897 | 36273 |
+----------------+----------+
1 row in set (29.06 sec)
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3327845
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: index
possible_keys: idx_app_promo
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
# Without ORDER BY in subquery
SELECT SQL_NO_CACHE a.application_id, (SELECT b.promo_id FROM b WHERE b.application_id = a.application_id LIMIT 1) AS promo_id FROM a WHERE Application_id > 1533456895 LIMIT 1 ;
+----------------+----------+
| application_id | promo_id |
+----------------+----------+
| 1533456897 | 36273 |
+----------------+----------+
1 row in set (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3327845
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: ref
possible_keys: idx_app_promo
key: idx_app_promo
key_len: 4
ref: olp.a.application_id
rows: 2
Extra: Using index
2 rows in set (0.00 sec)
# With ORDER BY and a FORCE hint
SELECT SQL_NO_CACHE a.application_id, (SELECT b.promo_id FROM b FORCE INDEX (idx_promo_id) WHERE b.application_id = a.application_id ORDER BY b.campaign_info_id LIMIT 1) AS promo_id FROM a WHERE Application_id > 1533456895 LIMIT 1 ;
+----------------+----------+
| application_id | promo_id |
+----------------+----------+
| 1533456897 | 36273 |
+----------------+----------+
1 row in set (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3327845
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: b
type: ref
possible_keys: idx_app_promo
key: idx_app_promo
key_len: 4
ref: olp.a.application_id
rows: 2
Extra: Using where; Using filesort
2 rows in set (0.00 sec)
####
I am running Percona Server version: 5.1.63rel13.4-log.
# Table schemas and cardinalities
CREATE TABLE `a` (
`application_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`application_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1540456896 DEFAULT CHARSET=latin1 PACK_KEYS=0
show indexes from a;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| a | 0 | PRIMARY | 1 | application_id | A | 32755854 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
CREATE TABLE `b` (
`campaign_info_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`application_id` int(10) unsigned NOT NULL DEFAULT '0',
`promo_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`campaign_info_id`),
KEY `idx_promo_id` (`promo_id`),
KEY `idx_app_promo` (`application_id`,`promo_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1731392426 DEFAULT CHARSET=latin1 PACK_KEYS=0
show indexes from b;
+-------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| b | 0 | PRIMARY | 1 | campaign_info_id | A | 52361822 | NULL | NULL | | BTREE | |
| b | 1 | idx_promo_id | 1 | promo_id | A | 1195 | NULL | NULL | | BTREE | |
| b | 1 | idx_app_promo | 1 | application_id | A | 26180911 | NULL | NULL | | BTREE | |
| b | 1 | idx_app_promo | 2 | promo_id | A | 52361822 | NULL | NULL | | BTREE | |
+-------+------------+---------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
Thanks,
-Eric
Eric
Nov 13, 2012, 6:40:24 PM11/13/12
to percona-d...@googlegroups.com
I think this is a regression bug.
My query works fine without the need of a hint on 5.0.58-enterprise-gpl MySQL Enterprise Server (GPL).
I will file or find a matching but with oracle.
Reply all
Reply to author
Forward
0 new messages