List:General Discussion« Previous MessageNext Message »
From:Kendall Gifford Date:January 24 2011 9:20pm
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 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.

Thanks for the help everybody.


>
> Regards,
> Jörg
>
>
-- 
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