List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:January 21 2011 7:56pm
Subject:Re: Slow query on MySQL4 server doing simple inner join of two InnoDB
tables
View as plain text  
you need hughe ram / innodb_buffer_pool for large datasets
in a perfect world the buffer_pool is as large as the data

how looks your current config?
how much RAM has the machine?

Am 21.01.2011 20:21, schrieb Kendall Gifford:
> 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)
> 
> These track information about email messages. Each message "has many"
> recipient records. The structure of the two tables (omitting irrelevant data
> fields) are as follows:
> 
>
> +-------------+------------------+------+-----+---------------------+----------------+
> | Field       | Type             | Null | Key | Default             |
> Extra          |
>
> +-------------+------------------+------+-----+---------------------+----------------+
> | id          | int(10) unsigned |      | PRI | NULL                |
> auto_increment |
> | sent_at     | datetime         |      | MUL | 0000-00-00 00:00:00
> |                |
> | ......................... OTHER FIELDS OMITTED FOR BREVITY
> ....................... |
>
> +-------------+------------------+------+-----+---------------------+----------------+
> 
>
> +-------------+------------------+------+-----+---------------------+----------------+
> | Field       | Type             | Null | Key | Default             |
> Extra          |
>
> +-------------+------------------+------+-----+---------------------+----------------+
> | id          | int(10) unsigned |      | PRI | NULL                |
> auto_increment |
> | message_id  | int(10) unsigned |      | MUL | 0
> |                |
> | employee_id | int(10) unsigned | YES  | MUL | NULL
> |                |
> | ......................... OTHER FIELDS OMITTED FOR BREVITY
> ....................... |
>
> +-------------+------------------+------+-----+---------------------+----------------+
> 
> 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. The query explanation is as follows:
> 
>
> +----+-------------+------------+--------+--------------------------+--------------+---------+-------------------------------------+--------+----------------------------------------------+
> | id | select_type | table      | type   | possible_keys            |
> key          | key_len | ref                                 | rows   |
> Extra                                        |
>
> +----+-------------+------------+--------+--------------------------+--------------+---------+-------------------------------------+--------+----------------------------------------------+
> |  1 | SIMPLE      | recipients | ref    | messages_fk,employee_idx |
> employee_idx |       5 | const                               | 222640 |
> Using where; Using temporary; Using filesort |
> |  1 | SIMPLE      | messages   | eq_ref | PRIMARY                  |
> PRIMARY      |       4 | email_archive.recipients.message_id |      1
> |                                              |
>
> +----+-------------+------------+--------+--------------------------+--------------+---------+-------------------------------------+--------+----------------------------------------------+
> 
> I've been doing some searching on the web and have no idea if/how this can
> be sped up. Most searches these days reference MySQL 5.x which I'm just not
> sure how much applies. I'm hoping that there is something obvious that I'm
> missing, or that one of you experts knows what I might be able to change to
> speed this query up.
> 
> Anyhow, thanks in advance for even so much as reading my message, let alone
> replying :).
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
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