Hi everybody!
Shawn Green (MySQL) wrote: > On 1/21/2011 14:21, Kendall Gifford wrote: >> Hello everyone, I've got a database on an old Fedora Core 4 server >> running >> MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question >> has >> just two (InnoDB) tables: >> >> messages (approx 2.5 million records) >> recipients (approx 6.5 million records) >> >> [[ ... see the original post for the schema details ... ]] >> >> >> I have the following query that is just too slow: >> >>> SELECT messages.* FROM messages >>> INNER JOIN recipients ON recipients.message_id = messages.id >>> WHERE recipients.employee_id = X >>> GROUP BY messages.id >>> ORDER BY sent_at DESC >>> LIMIT 0, 25; >> >> This takes about 44 seconds on average. [[...]] >> > > You need to get rid of the GROUP BY to make this go faster. You can do > that by running two queries, one to pick the list of unique > recipients.message_id values that match your where condition then > another to actually retrieve the message data. [[...]] I don't want to contradict Shawn, but currently I fail to see the need for the GROUP BY: Joining like this messages INNER JOIN recipients ON recipients.message_id = messages.id WHERE recipients.employee_id = X can return only one row, unless there are multiple "recipients" records for the same values of "message_id" and "employee_id". I don't know whether that can happen in the poster's application, and whether it would cause trouble if the result line would occur multiple times. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
