Hi!
>>>>> "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> least...in 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>
> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
Eric> | table | type | possible_keys | key | key_len |
Eric> ref | rows | Extra |
Eric>
> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
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>
> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+
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'"
Regards,
Monty
