Horrible MySQL index behavior with a simplest IN statement

Question

I have found that MySQL (Win 7 64, 5.6.14) does not use index properly if I specify table output for IN statement. USER table contains 900k records.

1. If I use IN (_SOME_TABLE_OUTPUT_) syntax - I get fullscan for all 900k users. Query runs forever.
2. If I use IN ('CONCRETE','VALUES') syntax - I get a correct index usage.

How can I make MySQL finally USE the index?

1st case:

explain SELECT gu.id FROM USER gu WHERE gu.uuid in 
    (select '11b6a540-0dc5-44e0-877d-b3b83f331231' union 
     select '11b6a540-0dc5-44e0-877d-b3b83f331232');

+----+--------------------+------------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type        | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+--------------------+------------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | PRIMARY            | gu         | index | NULL          | uuid | 257     | NULL | 829930 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | NULL       | NULL  | NULL          | NULL | NULL    | NULL |   NULL | No tables used           |
|  3 | DEPENDENT UNION    | NULL       | NULL  | NULL          | NULL | NULL    | NULL |   NULL | No tables used           |
| NULL | UNION RESULT     | <union2,3> | ALL   | NULL          | NULL | NULL    | NULL |   NULL | Using temporary          |
+----+--------------------+------------+-------+---------------+------+---------+------+--------+--------------------------+

2nd case:

explain SELECT gu.id FROM USER gu WHERE gu.uuid in 
    ('11b6a540-0dc5-44e0-877d-b3b83f331231');

+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | gu    | ref  | uuid          | uuid | 257     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

Table structure:

CREATE TABLE `USER` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `partner_id` bigint(20) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique-email` (`partner_id`,`email`),
  KEY `uuid` (`uuid`),
  CONSTRAINT `fk_USER_partner` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FKB2D9FEBE725C505E` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3315452 DEFAULT CHARSET=latin1

FORCE INDEX and USE INDEX statements don't change anything.

Demonstration SQLfiddle: http://sqlfiddle.com/#!2/c607e1/2

Answer 1

In fact I faced such problem before and it happened that I had one table that had a single column set as UTF-8 and the other tables where latin1. It did not matter what I did, MySQL insisted on using no indexes. The problem is quite well described on this blog post Slow queries in MySQL due to collation problems. Once you manage to fix the character set, I believe any of the queries will work.

Answer 2

An inner join on your virtual table might give you better performance. Try something along these lines.

SELECT gu.id 
FROM USER gu 
INNER JOIN (
  select '11b6a540-0dc5-44e0-877d-b3b83f331231' uuid
  union all
  select '11b6a540-0dc5-44e0-877d-b3b83f331232') ids
on gu.uuid = ids.uuid;