Anfrage zu einem Explain Join!

[Technik via echtwahr.com]

Hallo Liste,

ich hab da mal eine Frage:
Wieso dauert mein Left Join:
select count(*), a.itCategory, b.catName, b.catChild from tblCats as b left
join tblItems as a on ( a.itCategory = b.catChild and a.itClosedflag=0 and
a.itSeller = 11442 ) where  b.catParent = 21234 group by a.itCategory order
by b.catName;
ganze
13 rows in set (18.61 sec) (Eine NULL Reihe)

mysql> explain select count(*), a.itCategory, b.catName, b.catChild from
tblCats as b left join tblItems as a on ( a.itCategory = b.catChild and
a.itClosedflag=0 and a.itSeller = 11442 ) where  b.catParent = 21234 group
by a.itCategory order by b.catName;
+----------+------+----------------------------------+-----------+---------+
-------+------+---------------------------------------------+
| table    | type | possible_keys                    | key       | key_len |
ref   | rows | Extra                                       |
+----------+------+----------------------------------+-----------+---------+
-------+------+---------------------------------------------+
| tblCats  | ref  | catParent                                 | catParent  |
5      | const      |    9     | where used; Using temporary; Using filesort
|
| tblItems | ref  | itSeller,itClosedflag,itCategory | itSeller      |
4     | const     |   10 |                                             |
+----------+------+----------------------------------+-----------+---------+
-------+------+---------------------------------------------+


Ein Join der die selben zeilen zurüclk liefert aber mehr zeilen durchsuchen
muß:
explain select count(*), a.itCategory, b.catName, b.catChild from tblItems
as a  left join tblCats as b  on ( b.catParent = 21234 ) where a.itCategory
= b.catChild and a.itClosedflag=0 and a.itSeller = 11442 group by
a.itCategory order by b.catName;
+----------+------+----------------------------------+-----------+---------+
-------+-------+---------------------------------------------+
| table    | type | possible_keys                    | key       | key_len |
ref   | rows  | Extra                                       |
+----------+------+----------------------------------+-----------+---------+
-------+-------+---------------------------------------------+
| tblItems | ref  | itSeller,itClosedflag,itCategory | itSeller     |
4 | const | 77720  | where used; Using temporary; Using filesort |
| tblCats  | ref  | catParent                                | catParent  |
5 | const |    10    | where used                                  |


select count(*), a.itCategory, b.catName, b.catChild from tblItems as a
left join tblCats as b  on ( b.catParent = 21234 ) where a.itCategory =
b.catChild and a.itClosedflag=0 and a.itSeller = 11442 group by a.itCategory
order by b.catName;

"Nur"
12 rows in set (6.82 sec)



Was ich nicht verstehe, bei der 1 abfrage sag der Optimizer das er nur 9 *
10 Zeilen lesen muß!
bei der 2. abfrage muß er 77720 * 10 lesen, ist aber schneller?!

Ich hatte mir die Bücher O'reilly  Mysql und Mysql Offizell gekauft,
aber zu Joins steht da ja absolut nichts erklärendes drin :-((

Vielleicht kann mir mal jemand erklären, weshalb einen durchsungen von 9 *
10 länger benötigt als 77720 * 10?


Gruß Thomas
(Spanien)


-- 
Infos zur Mailingliste, zur Teilnahme und zum An- und Abmelden unter
-->>  http://www.4t2.com/mysql