>>>>> "Eric" == Eric <[EMAIL PROTECTED]> 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 cases. 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> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ Eric> | table | type | possible_keys | key | key_len | Eric> ref | rows | Extra | Eric> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ 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> +-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ 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 bit. 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`), to 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`) to KEY numndx (num, type) <cut> Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
