MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk

Question

There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table.

table_name	index_name	stat_name	stat_value	sample_size	stat_description
dept	PRIMARY	size	1	NULL	Number of pages in the index

After running the following query...

select * from dept;

..and looking at its optimizer trace, I see that in the row estimation phase, it says cost = 1 (reading 1 block), but in the planning phase, it says 21. I do not know why and what is the calculation there.

The same happens for all tables. Another Example:

There is a table student and it has 100000 rows, tucked in 609 blocks, as seen in the mysql.innodb_index_stats table.

table_name	index_name	stat_name	stat_value	sample_size	stat_description
student	PRIMARY	size	609	NULL	Number of pages in the index

After running the following query...

select * from student;

..and looking at its optimizer trace, I see that in the row estimation phase, it says cost = 609 (reading 609 blocks), but in the planning phase, it says 20504.

Answer 1

In addition to the IO cost, a compute cost is added. This cost is #rows * row_evaluate_cost. By default row_evaluate_cost = 0.2. In your case, the are 100 rows, so the compute cost will be 1 + 100*0.2 = 21.

Answer 2

When the Cost model was implemented, complained (to no avail) that it failed to take into effect

• Whether the data was likely to be cached in RAM. (This makes a big difference.)
• Whether the disk is HDD or SSD. (Possibly a factor of 10 on the cost.)

Hence, I mostly ignore "cost". Instead, I look primarily at "number of rows (data and/or index) touched". This is somewhat provided by

FLUSH STATUS;
run the query
SHOW SESSION STATUS LIKE 'Handler%';

This fails to note UUID indexes are likely to be very non-clustered as opposed to, say, a date range.

UUID (or hash) + huge table (to big to be cached in RAM) ==> Terrible Performance.