List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 4 2000 4:21pm
Subject:optimizer could be improved, I think
View as plain text  
>>>>> "Benjamin" == Benjamin Pflugmann <philemon@stripped> writes:

Benjamin> Hi.
Benjamin> While answering to an email, I just noticed that the MySQL optimizer
Benjamin> (3.22.21) doesn't do the optimal thing, although I think it would not
Benjamin> be too hard to detect. Correct me, if I am wrong. Ah, and I don't have
Benjamin> an 3.23.x version at hand, so could someone check whether this has
Benjamin> been improved meanwhile? Thanks.

Benjamin> The EXPLAIN tells:

mysql> EXPLAIN SELECT LENGTH(s.gid) FROM numbers f, numbers s WHERE s.gid=f.gid+1;
> +-------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
Benjamin> | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra   
>                                     |
> +-------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
Benjamin> | f     | index | NULL          | PRIMARY |       4 | NULL | 10000 | Using
> index                                  |
Benjamin> | s     | ALL   | PRIMARY       | NULL    |    NULL | NULL | 10000 | range
> checked for each record (index map: 1) |
> +-------+-------+---------------+---------+---------+------+-------+----------------------------------------------+
Benjamin> 2 rows in set (0.00 sec)


MySQL 3.23 can optimize 'key_column operator expression' type of where
clauses, so the above will be on in 3.23.

Benjamin> I assume, the reason that it is not optimized is that (f.gid+1) is an
Benjamin> expression. But why does it say "range checked..."? It is an equal
Benjamin> comparison, therefore there is no need for a *range* check. Or is this
Benjamin> just a common description for such queries with expressions?

'range checked for each table' means that for each row in 'f', MySQL
will do a quick check of which index it can use and if it's worth to
use it (instead of a full table scan).  The above means that the above
will be 2-3 times slower than a 'eq_ref', but not as bad as a full
table scan.

Benjamin> I guess, it is quite easy to recognize that f.gid+1 could be almost
Benjamin> handled like f.gid, since it will always be an exact reference to
Benjamin> another table.

Just try 3.23 :)

Benjamin> OTOH, I could be dead wrong and it quite difficult to check. Well,
Benjamin> this completely depends on how the parser/optimizer works.

The problem was that the old UNIREG code had shared buffers between
tables for the key reference;  This was very efficient but didn't
allow anything else than key=column or key=constant optimization.  In
MySQL 3.23 this is now dynamic and as an extra bonus the new code can also
handle NULL in indexes.

optimizer could be improved, I thinkBenjamin Pflugmann4 Jan
  • optimizer could be improved, I thinkMichael Widenius4 Jan
  • Re: optimizer could be improved, I thinkSasha Pachev4 Jan