Description:
The query planner will calculate the cost of a query plan.
One important part of the cost model is the estimated rows read from the disk.
For example, this part of the cost of the following plan is :
io_block_read_cost * Σ(rows * filtered)
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
| 1 | SIMPLE | PART | NULL | ALL | PRIMARY | NULL | NULL | NULL | 198000 | 1.33 | Using where |
| 1 | SIMPLE | LINEITEM | NULL | ref | LINEITEM_FK2 | LINEITEM_FK2 | 4 | tpch.PART.P_PARTKEY | 30 | 0.67 | Using where |
+----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+
However, no matter these reads are sequential or random, the cost will be same.
Given that the speed of sequential read and random read can be very different in different storage media.
If the query planer treats them in the same way, there are more likely that a sub-optimal query plan can be chosen, for example, I encounter this case:
bug #103272
I think this is not a corner case.
I noticed that the `engine_cost` table contains a `device_type` column,
could I expect MySQL will take these different read characteristics among different devices into account?
Thanks!
How to repeat:
None.
Suggested fix:
Make query planer aware of the read pattern (random/sequential)
Description: The query planner will calculate the cost of a query plan. One important part of the cost model is the estimated rows read from the disk. For example, this part of the cost of the following plan is : io_block_read_cost * Σ(rows * filtered) +----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+ | 1 | SIMPLE | PART | NULL | ALL | PRIMARY | NULL | NULL | NULL | 198000 | 1.33 | Using where | | 1 | SIMPLE | LINEITEM | NULL | ref | LINEITEM_FK2 | LINEITEM_FK2 | 4 | tpch.PART.P_PARTKEY | 30 | 0.67 | Using where | +----+-------------+----------+------------+------+---------------+--------------+---------+---------------------+--------+----------+-------------+ However, no matter these reads are sequential or random, the cost will be same. Given that the speed of sequential read and random read can be very different in different storage media. If the query planer treats them in the same way, there are more likely that a sub-optimal query plan can be chosen, for example, I encounter this case: bug #103272 I think this is not a corner case. I noticed that the `engine_cost` table contains a `device_type` column, could I expect MySQL will take these different read characteristics among different devices into account? Thanks! How to repeat: None. Suggested fix: Make query planer aware of the read pattern (random/sequential)