Adding limit clause to MySQL sql makes it slower

[sunmingtao]

change from

select distinct id from work_history wh
 WHERE (wh.txn_end   BETWEEN 256637665 AND 261848108 OR wh.txn_start   BETWEEN 256637665 AND 261848108)
 and collection = 'nla.aus' and accessConditions NOT IN ('Restricted', 'Internal Access Only') AND wh.form IN ( 'Journal' ) and wh.bibLevel = 'Item'
 ORDER BY id  LIMIT 0, 50;

to

select wh.id from ( select distinct id from work_history wh
 WHERE (wh.txn_end   BETWEEN 256637665 AND 261848108 OR wh.txn_start   BETWEEN 256637665 AND 261848108)
 and collection = 'nla.aus' and accessConditions NOT IN ('Restricted', 'Internal Access Only') AND wh.form IN ( 'Journal' ) and wh.bibLevel = 'Item'
 ORDER BY id ) wh LIMIT 0, 50;

It looks counter-intuitive but surprisingly the first query doesn't use index but the second one (the inner subquery) does.

When a query mysteriously becomes faster, it can mysteriously become slow.

So for the reference,

explain select distinct id from work_history wh
 WHERE (wh.txn_end   BETWEEN 256637665 AND 261848108 OR wh.txn_start   BETWEEN 256637665 AND 261848108)
 and collection = 'nla.aus' and accessConditions NOT IN ('Restricted', 'Internal Access Only') AND wh.form IN ( 'Journal' ) and wh.bibLevel = 'Item'
 ORDER BY id

shows

'1','SIMPLE','wh',NULL,'index_merge','PRIMARY,work_history_id,work_history_restricted_child,work_history_digitalStatus,work_history_txn_end,wh_txn_start,wh_type,collection_idx','work_history_txn_end,wh_txn_start','8,8',NULL,'2932556','0.27','Using sort_union(work_history_txn_end,wh_txn_start); Using where; Using temporary; Using filesort'

https://www.percona.com/blog/2009/09/19/multi-column-indexes-vs-index-merge/