Performance of Query differs a lot even though the execution plan is same

[Amit Gorain]

Hi,
Even though the execution plan is same(same index used, same number of row scans and returned), I can see huge performance difference while executing the same query with USE INDEX and IGNORE INDEX.

In both the cases, the same index is being used by the query optimiser.


USE INDEX QUERY :-

Execution Time:- 8 seconds

SELECT kb_knowledge0.`kb_knowledge_base`
FROM (kb_knowledge kb_knowledge0 use index(idx_kb_knowledge_sys_class_name_valid_to_workflow_state) left JOIN kb_knowledge_base kb_knowledge_base1 ON kb_knowledge0.`kb_knowledge_base` = kb_knowledge_base1.`sys_id`)
WHERE kb_knowledge0.`sys_class_name` != 'kb_knowledge_block' AND kb_knowledge0.`valid_to` >= '2018-06-21'
AND kb_knowledge0.`workflow_state` = 'published' AND kb_knowledge_base1.`active` = 1 GROUP BY kb_knowledge0.`kb_knowledge_base`,kb_knowledge_base1.`title`
ORDER BY kb_knowledge_base1.`title`;

IGNORE INDEX QUERY :-

Execution Time: 18 seconds

SELECT kb_knowledge0.`kb_knowledge_base`
FROM (kb_knowledge kb_knowledge0 ignore index(snow_2647_1530814509641_13) left JOIN kb_knowledge_base kb_knowledge_base1 ON kb_knowledge0.`kb_knowledge_base` = kb_knowledge_base1.`sys_id`)
WHERE kb_knowledge0.`sys_class_name` != 'kb_knowledge_block' AND kb_knowledge0.`valid_to` >= '2018-06-21'
AND kb_knowledge0.`workflow_state` = 'published' AND kb_knowledge_base1.`active` = 1 GROUP BY kb_knowledge0.`kb_knowledge_base`,kb_knowledge_base1.`title`
ORDER BY kb_knowledge_base1.`title`;