Why doesn't my query use any indexes when I use a subquery?

Question

I have the following tables (deleted columns that are not 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)) ) 

I am currently using this query, which works 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 would like to expand this query to also consider aliases.

Firstly, I tried using a connection:

 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'; 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) 

It looks bad: no index, 87401 rows using temporary. Using a temporary object only appears when I use distinct , but since the alias can be the same as the name, I cannot get rid of it.

Next, I tried replacing the connection 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, 540,309 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 very well.

Why doesn't MySQL use indexes for both of my queries? What else could I do? Currently, it is best to extract person.ids for aliases and add them statically as (...) to the second query. Of course, there must be another way to do this with a single request. However, I am not currently up to date. Can I somehow get MySQL to use a different (better) query plan?

Answer 1

Try:

 SELECT p.* from person p WHERE p.name = 'John Mayer' or p.sortname = 'John Mayer' UNION SELECT p.* from person p, personalias a WHERE p.id =a.person and a.name = 'John Mayer' 

UNION will take care of distinctness.

Answer 2

There is only one table in the first query.

MySQL uses index merge : it takes row pointers from two indices and concatenates them.

The second query represents another table. MySQL cannot combine an index from another table, because record pointers are different.

You need to imitate this:

 SELECT p.* FROM ( SELECT id FROM person p WHERE p.name = 'John Mayer' OR p.sortname = 'John Mayer' UNION SELECT person FROM personalias a WHERE a.name = 'John Mayer' ) q JOIN person p ON p.id = q.id 

If your tables are MyISAM , include id as a trailing column in the indexes:

 CREATE INDEX ix_person_name_id ON (name, id); CREATE INDEX ix_person_sortname_id ON (sortname, id); CREATE INDEX ix_personalias_name_person (name, person); 

Also note that for such queries it is better to use FULLTEXT indices:

 CREATE FULLTEXT INDEX fx_person_name_sortname ON person (name, sortname); SELECT p.* FROM ( SELECT id FROM person p WHERE MATCH (name, sortname) AGAINST ('"John Mayer"' IN BOOLEAN MODE) UNION SELECT person FROM personalias a WHERE a.name = 'John Mayer' ) q JOIN person p ON p.id = q.id