MySQL Forums
Forum List  »  Performance

MyISAM move from 5.1 to 5.7 - performance simple SELECT
Posted by: Christine Antoine
Date: May 13, 2020 03:02AM

Hi,

I moved a MyISAM DB from 5.1 to 5.7 and I have strange performance issue on a select.

1-/ Index on the query
5.7 engine is not using the index and prefer doing a full scan while it's not the case in 5.1.
It's using the index on the file table in 5.1.

5.7
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'files', NULL, 'ALL', 'PRIMARY', NULL, NULL, NULL, '944', '0.11', 'Using where'
'1', 'SIMPLE', 'rte_events', NULL, 'ref', 'event_type', 'event_type', '12', 'const', '4554999', '0.33', 'Using where'

5.1
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'rte_events', 'ref', 'event_type', 'event_type', '12', 'const', '2477898', 'Using where'
'1', 'SIMPLE', 'files', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'patprod.rte_events.file_id', '1', 'Using where'


2-/ Performance
The query in 5.1 via Workbench = around 40 seconds
The query in 5.1 via cli = around 40 seconds
The query in 5.7 via cli= around 40 seconds
The query in 5.7 via Workbench = > 600 seconds


Why such a difference between cli and workbench ?

Many thanks for the help !

Options: ReplyQuote


Subject
Views
Written By
Posted
MyISAM move from 5.1 to 5.7 - performance simple SELECT
312
May 13, 2020 03:02AM


Sorry, you can't reply to this topic. It has been closed.

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.