MySql Server System Variables - max-seek-for-key - Practical Example
I am going through the documentation on optimizing MySql indexes.
I found the -max-seekks-for-key = 1000 option. I checked the MySQL "server system variables" here .
According to him
"By setting this to a low value (say 100), you can force MySQL to prefer indexes over table scans."
...
What I understand from FULL TABLE SCAN:
When a query needs to access most of the rows, reading is consistently faster than working through the index. Sequential reads minimize disk searches, even if not all rows are required for the query.
So if MySQL is doing Full Table Scan, which minimizes disk, then why use -max-seekks-for-key = 1000 to prefer index scans, which can increase the number of read requests.
Here in the documentation 8.3.1.20 How to avoid full table scan mentioned as a step to avoid full scan: start mysqld with --max-seek-for-key = 1000
So I'm wondering if there is a practical and meaningful use of -max-seekks-for-key.
source to share
Well I have a real query executed by Magento, I am running MySQL 5.7
SELECT SQL_NO_CACHE
main_table.entity_id
FROM catalog_category_flat_store_2 AS main_table
LEFT JOIN core_url_rewrite AS url_rewrite ON
url_rewrite.category_id = main_table.entity_id
AND url_rewrite.is_system = 1
AND url_rewrite.store_id = 2
AND url_rewrite.id_path LIKE 'category/%'
WHERE main_table.include_in_menu = '1'
AND main_table.is_active = '1'
AND main_table.path LIKE '1/2/%'
ORDER BY main_table.position ASC
When I set max_seeks_for_key
to 670
or below, the request is done after 2 seconds. When the value is higher (very high by default), the request takes about 6 minutes .
Yes, I know this is a terrible request. I didn't write it, it was created by Magento ecommerce app.
I used EXPLAIN to find the difference. I can see that with a low value, max_seeks_for_key
it uses the index on the table core_url_rewrite
. This is not the case with a higher value.
MySQL 5.6 makes index usage for the same query without any configuration changes.
Additional context: The table catalog_category_flat_store_2
contains 732 records. The table core_url_rewrite
is 1.8 million records. The index is a non-ideal index on a field category_id
(JOIN field) with a cardinality of 571. The result is 629 rows.
Be sure to run ANALYSE TABLE
to help MySQL make the right decisions.
source to share