Sir, LEFT JOINs rule out the use of indices on the join column in the
left table, making LEFT JOINs inherently slow. Try putting indices on
the columns used in your WHERE and ORDER BY clauses. I'm not
promising it will help, but it is recommended in Paul's book.
Bob Hall
>Hi folks,
>
>
>after having carefully tuned all statements within a larger web based
>application rewriting SQL statements and optimizing them by adding indices,
>there are still two statements left, which are awkward concerning
>performance. Maybe I am just too blind to see how to get them working
>faster, but I am puzzled at the moment. Can someone help me out with the
>following two SELECTs and their EXPLAINs, please?
>
>The only thing which changes from query to query is the date used in the
>WHERE clause AND the combination of the two active flags in the WHERE
>condition, which may be permutated giving all four possible combinations
>(p=Y/d=Y, p=N/d=N, p=N/d=Y and p=Y/d=N), alas this should not affect the
>querys execution plan in any way, if I see it right.
>
>
>With my best regards
>... Ralph ...
>
>
>
>EXPLAIN
>SELECT DISTINCT
> p.dam_id,
> p.lastchanged
>FROM
> products p
>LEFT JOIN
> dams d
>ON
> (p.dam_id=d.dam_id)
>WHERE
> p.active='Y' AND
> d.active='Y' AND
> p.lastchanged>='2001-02-01'
>ORDER BY
> p.lastchanged ASC, d.dam ASC;
>
>+-------+------+-------------------+-------------------+---------+---
>-------+---
>---+---------------------------------------------+
>| table | type | possible_keys | key | key_len | ref
>| rows | Extra |
>+-------+------+-------------------+-------------------+---------+---
>-------+---
>---+---------------------------------------------+
>| p | ALL | NULL | NULL | NULL | NULL
>| 9137 | where used; Using temporary; Using filesort |
>| d | ref | dam_id_active_idx | dam_id_active_idx | 4 | p.dam_id
>| 1 | where used; Distinct |
>+-------+------+-------------------+-------------------+---------+---
>-------+---
>---+---------------------------------------------+
>2 rows in set (0.01 sec)
>
>
>EXPLAIN
>SELECT DISTINCT
> p.dam_id,
> p.lastchanged
>FROM
> products p
>LEFT JOIN
> dams d
>ON
> (p.dam_id=d.dam_id)
>LEFT JOIN
> gags g
>ON
> (d.dam_id=g.dam_id AND (ISNULL(g.year) AND ISNULL(g.product_id)) OR
>(NOT ISNULL(g.year) AND g.product_id=g.dam_id))
>WHERE
> p.active='Y' AND
> d.active='Y' AND
> p.lastchanged>='2001-02-01'
>ORDER BY
> p.lastchanged ASC,
> d.dam ASC;
>
>+-------+------+------------------------+-------------------+--------
>-+---------
>-+------+---------------------------------------------+
>| table | type | possible_keys | key | key_len | ref
>| rows | Extra |
>+-------+------+------------------------+-------------------+--------
>-+---------
>-+------+---------------------------------------------+
>| p | ALL | NULL | NULL | NULL |
>NULL | 9137 | where used; Using temporary; Using filesort |
>| d | ref | dam_id_active_idx | dam_id_active_idx | 4 |
>p.dam_id | 1 | where used; Distinct |
>| g | ALL | dam_id_year_active_idx | NULL | NULL |
>NULL | 287 | Distinct |
>+-------+------+------------------------+-------------------+--------
>-+---------
>-+------+---------------------------------------------+
>3 rows in set (0.00 sec)
>
>
>
>---------------------------------------------------------------------
>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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
