Why isn't my query using any indices when I use a subquery?
- by sfussenegger
I have the following tables (removed columns that aren't used for my examples):
CREATE TABLE `person` (
`id` int(11) NOT NULL,
`name` varchar(1024) NOT NULL,
`sortname` varchar(1024) NOT NULL,
PRIMARY KEY (`id`),
KEY `sortname` (`sortname`(255)),
KEY `name` (`name`(255))
);
CREATE TABLE `personalias` (
`id` int(11) NOT NULL,
`person` int(11) NOT NULL,
`name` varchar(1024) NOT NULL,
PRIMARY KEY (`id`),
KEY `person` (`person`),
KEY `name` (`name`(255))
)
Currently, I'm using this query which works just fine:
select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer';
mysql> explain select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer';
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | p | index_merge | name,sortname | name,sortname | 767,767 | NULL | 3 | Using sort_union(name,sortname); Using where |
+----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
Now I'd like to extend this query to also consider aliases.
First, I've tried using a join:
select p.* from person p join personalias a where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer';
mysql> explain select p.* from person p join personalias a on p.id = a.person where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer';
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
| 1 | SIMPLE | a | ALL | ref,name | NULL | NULL | NULL | 87401 | Using temporary |
| 1 | SIMPLE | p | eq_ref | PRIMARY,name,sortname | PRIMARY | 4 | musicbrainz.a.ref | 1 | Using where |
+----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+
2 rows in set (0.00 sec)
This looks bad: no index, 87401 rows, using temporary. Using temporary only appears when I use distinct, but as an alias might be the same as the name, I can't really get rid of it.
Next, I've tried to replace the join with a subquery:
select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select person from personalias a where a.name = 'John Mayer');
mysql> explain select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select id from personalias a where a.name = 'John Mayer');
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
| 1 | PRIMARY | p | ALL | name,sortname | NULL | NULL | NULL | 540309 | Using where |
| 2 | DEPENDENT SUBQUERY | a | index_subquery | person,name | person | 4 | func | 1 | Using where |
+----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+
2 rows in set (0.00 sec)
Again, this looks pretty bad: no index, 540309 rows. Interestingly, both queries (select p.* from person ... or p.id in (4711,12345) and select id from personalias a where a.name = 'John Mayer') work extremely well.
Why doesn't MySQL use any indices for both of my queries? What else could I do? Currently, it looks best to fetch person.ids for aliases and add them statically as an in(...) to the second query. There certainly has to be another way to do this with a single query. I'm currently out of ideas though. Could I somehow force MySQL into using another (better) query plan?
