Hi, Zardosht! First: please send questions like this to the list, not only to me. I wanted to discuss this with Sergey Petrunia and had to paste this on irc. Anyway, On Oct 22, Zardosht Kasheff wrote:
Hello Sergei,
One of the problems we at Tokutek have run into with the optimizer many times is that the optimizer does not always use these functions to estimate costs. I think I have found a couple of places in the code, and want to verify that my understanding is correct.
In best_access_path in sql_select.cc, I see the following code in two places: if (table->covering_keys.is_set(key)) { /* we can use only index tree */ uint keys_per_block= table->file->stats.block_size/2/ (keyinfo->key_length+table->file->ref_length)+1; tmp= record_count*(tmp+keys_per_block-1)/keys_per_block; } else tmp= record_count*min(tmp,s->worst_seeks);
is there a reason why tmp cannot be evaluated using the functions keyread_time and read_time? If so, is there any reason why MariaDB or MySQL cannot use the functions in this place?
I cannot fix it in 5.1, unfortunately. The code should probably be rewritten as if (table->covering_keys.is_set(key)) { tmp= record_count*table->file->keyread_time(key, 1, tmp); } else tmp= record_count*table->file->read_time(key, 1, min(tmp,s->worst_seeks)); but tmp is double, while the third argument of keyread_time and read_time is ha_rows - an integer. That is, this change causes a small precision loss, and it is enough for optimizer to start generating different query plans. I've got quite a few test failures because EXPLAIN output changed. We cannot do changes that affect query plans in a stable version. One thing I've learned in MySQL - no matter how correct and good such a change is, there will always be queries that it will affect adversely. And it won't matter that the new plan is correct, and the old plan was completely illogical and caused by a bug in the optimizer or something - they'll say that their queries were faster that way. I'll see about doing this change it 5.2 though. Regards, Sergei