0

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.

enter image description here

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.

enter image description here

2 Answers 2

3

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.