mysql - Why does the query execute so much slower when all the columns involved are the same and only the where condition changes? -

i have query:

select 1 inputindex,        if(trim(deviceinput1name = '', 0, if(instr(deviceinput1name, '|') > 0, 2, 1)) inputtype,        (select value1_1 devicevalues deviceid = devices.deviceid order valuetime desc limit 1) inputvalueleft,        (select value1_2 devicevalues deviceid = devices.deviceid order valuetime desc limit 1) inputvalueright   devices  deviceimei = 'some_search_value'; 

this completes (in 0.01 seconds). however, running same query where clause such

 deviceimei = 'some_other_search_value'; 

makes run upwards of 14 seconds! search values finish quickly, while others run way long.

if run explain on either query, following:

+----+--------------------+--------------+-------+---------------+------------+---------+-------+------+-------------+ | id | select_type        | table        | type  | possible_keys | key        | key_len | ref   | rows |       | +----+--------------------+--------------+-------+---------------+------------+---------+-------+------+-------------+ |  1 | primary            | devices      | ref   | deviceimei    | deviceimei | 28      | const |    1 | using | |  3 | dependent subquery | devicevalues | index | deviceid,more | valuetime  | 9       | null  |    1 | using | |  2 | dependent subquery | devicevalues | index | deviceid,more | valuetime  | 9       | null  |    1 | using | +----+--------------------+--------------+-------+---------------+------------+---------+-------+------+-------------+ 

also, here's actual number of records, it's clear:

mysql> select count(*) devicevalues inner join devices using(deviceid) devices.deviceimei = 'some_search_value'; +----------+ | count(*) | +----------+ |  1017946 | +----------+ 1 row in set (0.17 sec)  mysql> select count(*) devicevalues inner join devices using(deviceid) devices.deviceimei = 'some_other_search_value'; +----------+ | count(*) | +----------+ |   306100 | +----------+ 1 row in set (0.04 sec) 

any ideas why changing search value in where clause cause query execute slowly, when number of physical records search through lower?

note there no need rewrite query, explain why above happens.

update: have tried running 2 separate queries instead of 1 dependent subqueries information need (first select deviceid devices deviceimei, select devicevalues deviceid got previous query) , queries return instantly. suppose solution run these queries in transaction, i'll making stored procedure this. this, however, still doesn't answer question puzzles me.

i dont think 1017946 equivalent number of rows returned first query.your first query returns rows devices correlated queries,your count query returns common rows between 2 tables.if problem might cardinality issues namely some_other_values constitute larger proportion of rows in first query some_value mysql chooses table scan.