I love when this happens. I woke in the middle of the night with an idea for you. It now occurs to me that the query you want is dead simple. It just took me a while to see:
SELECT * FROM messages WHERE id_from = 1 AND id_to = 2 UNION SELECT * FROM messages WHERE id_from = 2 AND id_to = 1 ORDER BY create_time Assuming an index on id_from (or id_from, id_to), it will be used. This will be very quick. hth, Arthur On 3/16/08, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > On Sun, Mar 16, 2008 at 10:36 AM, Velen <[EMAIL PROTECTED]> wrote: > > I would suggest u use > > > > SELECT * > > FROM messages > > WHERE id_from between 1 and 2 > > AND id_to between 1 and 2 > > ORDER BY time > > > That would only be applicable if he mandated that his users only send > messages to other users with user_id's +/- 1 of their own, or they > will break their message search app. > > I bet that would go over well. > > Even if this were not an issue changing from the IN to BETWEEN does > not help performance. In fact performance is worse. With the index I > had suggested above and using the sample data I created his original > query examines 28 rows, while yours examines 713, and takes several (4 > to 6) times longer to retrieve the rows on 5.0.51. > > -- > > Rob Wultsch > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
