From: Joerg Bruehe
Date: January 24 2011 10:40am
Subject: Re: Slow query on MySQL4 server doing simple inner join of two InnoDB
 tables
List-Archive: http://lists.mysql.com/mysql/224197
Message-Id: <4D3D571D.50202@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

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 =3D messages.id
>>> WHERE recipients.employee_id =3D X
>>> GROUP BY messages.id
>>> ORDER BY sent_at DESC
>>> LIMIT 0, 25;
>>
>> This takes about 44 seconds on average. [[...]]
>>
>=20
> 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 =3D messages.id=

  WHERE recipients.employee_id =3D 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=F6rg

--=20
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