Group by querying two tables

Hi guys,

Group by seems fairly straightforward when querying one table, but two is something I'm not having luck with. I've tried:

$quick_query = "SELECT * FROM c_tbl c, p_tbl p WHERE c.active = 1 AND p.active = 1 AND c.id = p.id AND c.code like '" . $cat . "'";
$quick_query .= " GROUP BY p.my_list";

And

$quick_query = "SELECT c.active, p.active, c.id, p.id, c.code, p.my_list FROM c_tbl c, p_tbl p WHERE c.active = 1 AND p.active = 1 AND c.id = p.id AND c.code like '" . $cat . "'";
$quick_query .= " GROUP BY p.my_list";

Both time out or give error messages if changed ever so slightly.

Any help?

Rob

 

Error messages would be helpful.

Also, do an EXPLAIN on these to see why they time out. Your indices are probably not optimized.

 

Error messages are usually when it's not sure which id it should get (or that I'm requesting). With those queries, it's just timeouts. And without the group by part, they pop right up, so I don't think it's an optimization issue.

 

I did an EXPLAIN and EXPLAIN EXTENDED. They both gave the following:

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 2096 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 7315 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Does this tell me anything?

 

If I remove the GROUP BY part of the statement, it works just fine. What gives?

 

It would seem you were correct. Once I added comparison indexes to both tables, the problem went away - started working is what I mean. Thank you for your help.