Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables

[Joerg Bruehe]

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.bruehe@stripped
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