MySQL Forums
Forum List  »  MySQL in Oracle Cloud

Performance of Query differs a lot even though the execution plan is same
Posted by: Amit Gorain
Date: July 06, 2018 01:37AM

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`;

Options: ReplyQuote


Subject
Written By
Posted
Performance of Query differs a lot even though the execution plan is same
July 06, 2018 01:37AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.