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. Thanks for the help everybody. > > Regards, > Jörg > > -- Kendall Gifford zettab...@gmail.com
