Here is my problem: I have 2 tables, a parent table and a child table. The parent table has got 113 rows, the child table has got 3 000 000 rows.
parent: ------------------- | p_id | name | ------------------- | 1 | A | | 2 | B | | ... | ... | | 112 | C | | 113 | D | ------------------- child: ------------------ | c_id | p_id | ------------------ | 1 | 1 | | 2 | 56 | | ... | ... | |2999999| 2 | |3000000| 56 | ------------------ I want to get a list of all the parents (even the parents without child) with the number of children they've got. I use a LEFT JOIN in order to retrieve all the parents without exception : SELECT parent.p_id, COUNT(child.c_id) FROM parent LEFT JOIN child ON (parent. p_id = child.p_id) GROUP BY parent.p_id; This query takes 140 seconds to be executed and I got 70 results. Now if I use a basic JOIN like that: SELECT parent.p_id, COUNT(child.c_id) FROM parent JOIN child ON (parent.p_id = child.p_id) GROUP BY parent.p_id; The query takes now 13 seconds to finish!! But now I got only 67 results because the basic JOIN does not include the parents without children. What I don't understand is why the JOIN is far much quicker than the LEFT JOIN whereas the only difference is that the LEFT JOIN includes the parents without children? Any explanations? Here are the EXPLAIN for the 2 cases : LEFT JOIN case : ------------------------------------------------------------------------------- table type possible_keys key key_len ref rows Extra parent index NULL PRIMARY 4 NULL 113 Using index child ref p_id p_id 5 parent.p_id 40694 ------------------------------------------------------------------------------- JOIN case: ------------------------------------------------------------------------------- table type possible_keys key key_len ref rows Extra child ALL p_id NULL NULL NULL 3000000 Using temporary; Using filesort parent eq_ref PRIMARY PRIMARY 4 child.p_id 1 Using index ------------------------------------------------------------------------------- I'm using MySQL 4.0.13 and MyISAM tables. I'm using keys and Indexes. Thank you very much. Benjamin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
