MySql Server System Variables --max-seeks-for-key - Practical example

Question

I am going through documentation of MySql Indexes Optimization.

I found a setting --max-seeks-for-key=1000.I checked the "server system variables" of MySQL here.
According to it

"By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans"

.
What i understand from FULL TABLE SCAN is:

When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

Therefore if MySQL is doing Full Table Scan which minimize disk seeks why would one use --max-seeks-for-key=1000 to prefer index scan which may increase disk seeks.

Here at the documentation 8.3.1.20 How to Avoid Full Table Scans its mentioned as a step to avoid full-scan: Start mysqld with the --max-seeks-for-key=1000

So I am curious to know if there is any practical and meaningful use of --max-seeks-for-key.

Answer 1

Well, I have a real query here 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 lower, the query runs in 2 seconds. When the value is higher (very high by default), the query takes about 6 minutes.

Yes, I know it's an awful query. I did not write it, it's created by the Magento e-commerce application framework.

I used EXPLAIN to find the difference. I see with the low max_seeks_for_key value it uses an index for the core_url_rewrite table. With a higher value it doesn't.

MySQL 5.6 does use an index for the same query without any changes to the configuration.

Extra context: The catalog_category_flat_store_2 table contains 732 records. The table core_url_rewrite is 1.8 Million records. The index is a non-unique index on the category_id field (the JOIN field) with a cardinality of 571. The result is 629 rows.

Don't forget to run ANALYSE TABLE to help MySQL take the correct decisions.