Key Points :
Mysql version 5.6.33 on the both ( master & slave ) version are same .
cross validated show status o/p is same on both server .
But getting different execution plan on master & slave .
Slave is not using sorting & temporary while master is using for same query .
schema & index are same on both servers even also validated ordered .
Already run on three table analyse & optimise on master server but still planner is different than slave .

Query : explain extended select this_.solution_type as y0_, this_.lead_id as y1_, this_.created_at as y2_, this_.cust_id as y3_, this_.mobile_number as y4_, node2_.sub_stage as y5_, node2_.stage as y6_ from user_business_mapping this_ inner join workflow_status wfstatus1_ on this_.id=wfstatus1_.user_business_mapping_id inner join workflow_node node2_ on wfstatus1_.workflow_node_id=node2_.id where this_.created_at>=’2018-01-19′ and this_.created_at<='2018-01-25' and this_.solution_type='bc_merchant' and wfstatus1_.is_active=1 order by this_.id asc limit 5

Master server Execution Plan :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: node2_
type: index
possible_keys: PRIMARY
key: idx_wfn_stage_substage_is_active
key_len: 305
ref: NULL
rows: 43
filtered: 100.00
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: wfstatus1_
type: ref
possible_keys: FK_ws_rbsm,FK_wfs_wf_nodeId_idx
key: FK_wfs_wf_nodeId_idx
key_len: 9
ref: onboarding_engine.node2_.id
rows: 927
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: this_
type: eq_ref
possible_keys: PRIMARY,created_at_idx
key: PRIMARY
key_len: 8
ref: onboarding_engine.wfstatus1_.user_business_mapping_id
rows: 1
filtered: 100.00
Extra: Using where
3 rows in set, 1 warning (0.00 sec)

Slave Server Execution plan :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: this_
type: index
possible_keys: PRIMARY,created_at_idx
key: PRIMARY
key_len: 8
ref: NULL
rows: 5
filtered: 4705840.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: wfstatus1_
type: ref
possible_keys: FK_ws_rbsm,FK_wfs_wf_nodeId_idx
key: FK_ws_rbsm
key_len: 8
ref: onboarding_engine.this_.id
rows: 4
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: node2_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: onboarding_engine.wfstatus1_.workflow_node_id
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)

I am using php laravel5 framework whose database library (Eloquent ORM) and query builder does not allow to specify use index/force index in the queries. But it allows to write raw order by clauses so i just added order by id + 0 as in SOLUTION #6 to prevent it using the wrong index. Now my query executes in 0.002 seconds.

Thanks for this great analysis and clever solution coming from 2009!

Our solution in the end was to run one query to simply get the queries from the ordered query without any of the other fields in the select. Then run a WHERE IN query using those IDs.

SELECT id from (SELECT id FROM data
WHERE type=12345 AND level > 3)
ORDER BY id

This would work fast only if for type=12345 you just have few hundred row (say 3

I am not sure what would be the best solution for that but as of now these two comes to my mind:

SELECT id from data IGNORE INDEX (index_on_type_column)
WHERE type = 12345 AND level > 3
ORDER BY id LIMIT 1000;

SELECT id from data
WHERE id NOT IN (SELECT id FROM data
WHERE type != 12345) AND level > 3
ORDER BY id LIMIT 1000;

thanks – first time I encounter this variable. Interesting.

What could have also helped is if you had mentioned the Exec Time for each of the Solutions#.

It would have helped with the comparisons. But nevertheless, this gives us techniques which can put to practice.