Description:
Creation of an index on three text fields causes a query to fail.
The exact same query without an index suceeds.
How to repeat:
CREATE INDEX AND DO SELECT:
mysql> CREATE INDEX idxChild ON Child (other_code(10), CommonField(10), ChildField(10));
Query OK, 1089 rows affected (0.26 sec)
Records: 1089 Duplicates: 0 Warnings: 0
mysql> SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField='477' AND ChildField='165';
Empty set (0.00 sec)
mysql> EXPLAIN SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField='477' AND ChildField='165';
+-------+-------+---------------+------+---------+------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------+
| Child | const | idxChild | NULL | NULL | NULL | 1 | |
+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
SAME QUERY - JUST DROP QUOTE ON ONE FIELD:
mysql> SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField=477 AND ChildField='165';
+-----------+
| PlanLine1 |
+-----------+
| Tim Test |
+-----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField=477 AND ChildField='165';
+-------+------+---------------+----------+---------+-------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+----------+---------+-------+------+-------------+
| Child | ref | idxChild | idxChild | 12 | const | 37 | Using where |
+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
DROP INDEX:
mysql> drop index idxChild on Child;
Query OK, 1089 rows affected (0.19 sec)
Records: 1089 Duplicates: 0 Warnings: 0
mysql> SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField='477' AND ChildField='165';
+-----------+
| PlanLine1 |
+-----------+
| Tim Test |
+-----------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT PlanLine1 FROM Child WHERE other_code='807780' AND CommonField='477' AND ChildField='165';
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+-------------+
| Child | ALL | NULL | NULL | NULL | NULL | 1089 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
ALL THREE FIELDS ARE TEXT:
| ChildField | text | | | | |
| CommonField | text | | | | |
| other_code | text | | MUL | | |
