List:General Discussion« Previous MessageNext Message »
From:Kendall Gifford Date:January 24 2011 10:28pm
Subject:Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables
View as plain text  
On Mon, Jan 24, 2011 at 2:20 PM, Kendall Gifford <zettabyte@stripped>wrote:

>
>
> On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe <joerg.bruehe@stripped>wrote:
>
>> 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.
>>
>>
> In my application, there CAN in fact be several "recipients" records with
> both the same "message_id" foreign key value AND the same "employee_id"
> value (some employees may be a recipient of a message several times over via
> alternative addresses and/or aliases). However, as I rework things, I could
> probably rework application logic nuke the GROUP BY and just cope, in code,
> with these extra "messages" records in my result set. (Just FYI, the SQL
> query is simply the default query as created by rails or, more specifically,
> ActiveRecord 2.3.9 which I can/will-be optimizing).
>
> I will additionally be moving this database to a new server. However, for
> academic interest, I'll see if I can make time to post the query time(s)
> once I change the app, before moving the database to a new (and better
> configured) server.
>
>
Just an update for posterity, simply removing the GROUP BY clause of my
query above has, overall, no noticeable effect on performance. I suspect
server configuration, as pointed out by Reindl, is too much of a bottleneck
and is what I first need to change (working on that now). Perhaps the
removal of GROUP BY would/will be noticeable if the server configuration for
InnoDB tables wasn't so horrendous. I'll find out...

-- 
Kendall Gifford
zettabyte@stripped

Thread
Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesReindl Harald21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesMySQL)21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesJoerg Bruehe24 Jan
      • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
        • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
          • RE: Slow query on MySQL4 server doing simple inner join of twoInnoDB tablesGavin Towey25 Jan
            • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford25 Jan
  • InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMattia Merzi25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
      • Re: InnoDB and rsyncJohan De Meersman25 Jan
        • Re: InnoDB and rsyncReindl Harald25 Jan
        • RE: InnoDB and rsyncJerry Schwartz25 Jan
    • Re: InnoDB and rsyncSteve Musumeche25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
    • Re: InnoDB and rsyncJohan De Meersman26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncMichael Dykman28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncEric Bergen1 Feb