List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:October 17 2001 1:04pm
Subject:Cant optimize self-join order
View as plain text  

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

Eric> I'm having a problem with a type of query in which I have a number of
Eric> self-joins of a large table (allow me this architecture even though it
Eric> is bad).  MySQL is not optimizing the order of my self-joins in the
Eric> fact, it's joining as if I was using STRAIGHT_JOIN.  This
Eric> is a huge problem for me as some of the self-joined table aliases have
Eric> very bad selectivity in their part of the WHERE clause, so if MySQL
Eric> ends up doing a temporary table (which is how it's joining these) of
Eric> one of the bad ones, it ends up copying a _large_ number of rows from
Eric> my big table.  I can not simply correct these queries by organizing
Eric> the join order myself because these are generated on the fly.  Is
Eric> there a way to tell MySQL to try harder to optimize my self-join
Eric> order?  Else, where would I go about inserting code to try and do this
Eric> self-join optimization myself?

Eric> This is with MySQL 3.23.33 on Solaris 8...would MySQL 4 help at all?

Eric> Example (on a 1/1000 scale testbed of my real database):

Eric> SELECT DISTINCT queryTable0.num, queryTable0.value, queryTable1.value FROM
Eric> index queryTable0, index queryTable1 WHERE
Eric> queryTable0.path=24 AND queryTable0.type="E" AND
Eric> queryTable1.path=27 AND queryTable1.type="E" AND
Eric> queryTable0.num=queryTable1.num AND  
Eric> queryTable0.nvalue > 0.0 AND  queryTable0.nvalue <= 900000.0 AND
Eric> queryTable1.nvalue > 140.0 AND queryTable1.nvalue <= 200.0;

Eric> EXPLAIN says:

> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
Eric> | table       | type | possible_keys        | key        | key_len |
Eric> ref                | rows | Extra                       |
> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
Eric> | queryTable0 | ref  | pathndx,numndx | pathndx |       4 |
Eric> const              | 1152 | where used; Using temporary |
Eric> | queryTable1 | ref  | pathndx,numndx | numndx  |       4 |
Eric> queryTable0.num |   53 | where used                  |
> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
Eric> 2 rows in set (0.01 sec)

Eric> If I flip around "FROM index queryTable0, index queryTable1" to "FROM
Eric> index queryTable1, index queryTable0", it makes the temporary table
Eric> out of queryTable1, thus running about 100 times faster.  Can't MySQL
Eric> do this for me?

We have done some modifications to optimizer in 4.0, but nothing that
should affect this.

What is the output from EXPLAIN if you swap the tables ?
What is the output from "show create table 'index'"