MyISAM move from 5.1 to 5.7 - performance simple SELECT
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 !