MySQL query never end Below query execute within 0.003 seconds.

SELECT * FROM cs.cs_15 where instr like '%X_T%' and end_time&gt;'2016-09-22' LIMIT 0, 1000


But when running following query, it just keep running without end:

SELECT * FROM cs.cs_15 where instr like '%X_T%' limit 1


Anyone have idea why?

Table Structure:

    +------------+-------------+------+-----+-------------------+-------+
    | Field      | Type        | Null | Key | Default           | Extra |
    +------------+-------------+------+-----+-------------------+-------+
    | instr      | varchar(45) | NO   | PRI | NULL              |       |
    | start_time | timestamp   | YES  |     | NULL              |       |
    | end_time   | timestamp   | NO   | PRI | CURRENT_TIMESTAMP |       |
    | a          | float       | YES  |     | NULL              |       |
    | b          | float       | YES  |     | NULL              |       |
    | c          | float       | YES  |     | NULL              |       |
    | d          | float       | YES  |     | NULL              |       |
    | e          | float       | YES  |     | NULL              |       |
    | f          | varchar(45) | YES  |     | NULL              |       |
    +------------+-------------+------+-----+-------------------+-------+


Index:

+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cs_15           |          0 | PRIMARY    |            1 | instr       | A         |      172410 |     NULL | NULL   |      | BTREE      |         |               |
| cs_15           |          0 | PRIMARY    |            2 | end_time    | A         |    21551290 |     NULL | NULL   |      | BTREE      |         |               |
| cs_15           |          0 | unique_idx |            1 | end_time    | A         |     3078755 |     NULL | NULL   |      | BTREE      |         |               |
| cs_15           |          0 | unique_idx |            2 | instr       | A         |    21551290 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
You have UNIQUE(end_time, instr), correct?  Then the fast query (SELECT * FROM cs.cs_15 where instr like '%X_T%' and end_time&gt;'2016-09-22' LIMIT 0, 1000) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)


Reach into that index at '2016-09-22'.
Scan forward, looking for instr like '%X_T%'
peel off the first 1000 that match.


Note: Apparently many of the rows have X_T, so the query ends quickly.

The slow query (SELECT * FROM cs.cs_15 where instr like '%X_T%' limit 1) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)


No index is useful, so scan the entire table, looking for 1 row with instr like '%X_T%'.


Note: Apparently there is no such row early in the table.  That is, old end_times never had X_T.

There is not good way to speed up the slow query.  The leading wildcard in the LIKE make it useless to index that column.