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
