Date: 01/22/07 (MySQL Communtiy)    Keywords: mysql, sql

i have two tables, contacts and tasks. users create a contact and then create tasks for the contact. one contact can have many tasks. i want to retrieve the contacts and, if there are any active tasks, i want to retrieve only one. if there are no active tasks, i still want to get the contact. the idea is that users can see a list of contacts, and a little icon appears next to any that have pending task/s. i've been doing this (simplified a bit):

SELECT c.`id`,c.`name`, t.`id`, t.`task_type` FROM `contacts` AS `c` LEFT JOIN `tasks` AS `t` ON c.`id` = t.`contact_id` AND t.`done` = '0' WHERE 1 GROUP BY c.`id`

which was working, but lately is running very slow (mysql hangs for 2+ minutes at "copying to tmp table"). is there a more efficient way to do this? everything else i can come up with only returns contacts with active tasks. i had to toss that GROUP BY in there because otherwise i'd get a contact returned as many times as it had tasks, which is obviously not right and a bit hacky. so, i'm sure i'm doing something wrong here.

Source: http://community.livejournal.com/mysql/108677.html