List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 17 2000 2:57am
Subject:[rantapaa@oss.uswest.net: disjunctions and index usage]
View as plain text  
Hi!

>>>>> "Erik" == Erik E Rantapaa <rantapaa@stripped> writes:

Erik> Hi Monty,

Erik> I was wondering if you happen to see a posting I made recently to the
Erik> mysql mailing list.  It describes two similar situations. In one case
Erik> Mysql detects it can use the full index, but in the other it doesn't.

Erik> This is an optimization I would very much like to have, and would even
Erik> be willing to work on adding to Mysql. All I would need would be some 
Erik> pointers into the source with some ideas of what to do.

Everything happens in sql/sql_select.c

The problem here is that MySQL doesn't read the constant
tables during the optimization phase (make_join_statistics), but first
after this is complete;  The reason for this was mainly to avoid
problems when the rows in the constant table couldn't be found.

This means that the range optimize can't calculate exactly how many
rows it may find.  As there exist a 'not too bad' key to do the join,
MySQL will not try to check for any range keys as the check isn't that
fast.

To fix this you should need to examine the following functions (all in
sql_select.cc):

- make_join_statistics
- make_join_select
- join_read_const_tables

(It may be that you can do this only in make_join_select, but this may
not be optimal in all cases).

Regards,
Monty
Thread
[rantapaa@oss.uswest.net: disjunctions and index usage]Michael Widenius17 Feb