If you show the EXPLAIN SELECT .. output, and the table structure, someone will be able to give a more definite answer.
-----Original Message----- From: Kendall Gifford [mailto:zettab...@gmail.com] Sent: Monday, January 24, 2011 2:29 PM To: mysql@lists.mysql.com Subject: Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tables On Mon, Jan 24, 2011 at 2:20 PM, Kendall Gifford <zettab...@gmail.com>wrote: > > > On Mon, Jan 24, 2011 at 3:40 AM, Joerg Bruehe <joerg.bru...@oracle.com>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 zettab...@gmail.com IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
