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 :). -- Kendall Gifford zettab...@gmail.com
