List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:October 21 2001 6:53am
Subject:Re: Possible bug in self-join order optimization
View as plain text  

>>>>> "Eric" == Eric  <ej@stripped> writes:

Eric> Well, answering my own email, what I thought was a bug is not one at
Eric> all.  I was mistaken in thinking that MySQL paid any attention to the
Eric> WHERE conditions when optimizing the join order beyond determining
Eric> which keys are used for the join, correct?  

Yes. The value for the 'rows' column in EXPLAIN comes only from the
considered index

Eric> This is really terrible for queries like mine where the query could be
Eric> sped up by orders of magnitude if the join optimizer would just
Eric> determine which table in the join to scan and which to do the key
Eric> lookup on based on a more intelligent estimation of the number of rows
Eric> from each table.  It would have to go beyond looking at what keys are
Eric> used in the join (since each of the tables in my query can be looked
Eric> up by the same key) and account for the WHERE conditions placed on the
Eric> tables in the join.

In your case you should be able to get this done by using a multi-part
key, as I have suggested in an earlier email.
(You will probably get both this and the earlier email at the same
time as am writing this on my laptop without a internet connection...)

Eric> Is there sufficient metadata to estimate rows coming from a table
Eric> based on conditions placed on the attributes of that table?  Where is
Eric> it?  Has anyone ever thought of coding this?  Can anyone give me a
Eric> place to start?

To do this, we would first need to extend the MyISAM index file to
hold this information per column.

Before doing that, lets first see if we can fix this case by just
chaning the index a bit...

Eric> eric.