List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:October 20 2001 5:16pm
Subject:Re: Cant optimize self-join order
View as plain text  
>>>>> "Eric" == Eric  <ej@stripped> writes:

Eric> What is quite puzzling is MySQL's estimation of the number of rows
Eric> from each of the self-joins.  The conditions on alias queryTable0
Eric> actually refer to 1582 rows, and the conditions on alias queryTable1
Eric> refer to 39 rows.  Notice in the EXPLAIN below that when I flip around
Eric> the join order, MySQL thinks that 1152 (which is its estimation for
Eric> 1582) rows are coming from queryTable1, whereas with the original join
Eric> order, it thought 1152 rows were coming from queryTable0...this seems
Eric> like a bug to me since the conditions on those two aliases are the
Eric> same between the two queries.  Only the "FROM index queryTable0, index
Eric> queryTable1" is flipped to "FROM index queryTable1, index queryTable0".

Eric> See below,
Eric> eric.

It's actually quite clear why you get the same number of estimated rows in both

Because the alias are identically used and MySQL picks the same index
for both combinations, it will estimate the same number of rows in
both cases.

The query was:

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

Eric> EXPLAIN of swapped tables in FROM clause says:

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

The number 1152 comes from that MySQL estimates that there is about
1152 rows that can be found when searching through index 'path'
after the constant '24'.  MySQL does this estimation by doing a quick
lookup in the index tree.

The number 53, comes from MySQL guessing that there is about 53 rows
with identical 'num' values for each num.

You can get a much better guess for the second case if you run
'analyze table' on the table in question.

If you want to get the query faster, you have to improve your index a

If this is one of your typical queries, I would suggest you do the
follwing change to your index:

Change the index:

KEY `pathndx`(`path`),


KEY pathndx(path, type, nvalue)

If there for a 'num' is many rows with a different type, then you
should also change the key:

KEY `numndx`(`num`)


KEY numndx (num, type)



For technical support contracts, goto
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Michael Widenius <monty@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland