On Mon, Mar 17, 2008 at 4:36 AM, Arthur Fuller <[EMAIL PROTECTED]> wrote: > 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 time > > Assuming an index on id_from (or id_from, id_to), it will be used. This will > be very quick.
I bench'ed the union before sending in my original response. For the generic data set I created as an example his original query is faster (not by much) and simpler. If it were me writing the query I would use a union, probably. IN and OR never end well ;) >this will not prevent filesort, because the results still needs to be sorted for ORDER BY time, or? Yes. Adding the extra column to the index will not result in losing the filesort. The filesort will not be any sort of a problem unless the result is large. If you do a sort by the left most column of the index (in my suggestion id_from ) then you will not have a filesort, but it is not much faster. I suggest benching it. Create the table and populate them with my queries above. If you want a much larger dataset you can something like the following instead of my last 7 queries... INSERT INTO messages( id_from, id_to, text, time ) SELECT t1.id, t2.id, '', DATE_SUB( NOW( ) , INTERVAL t.i *10 + u.i DAY ) FROM user_ids t1, user_ids t2, integers AS u, integers AS t WHERE ( t.i *10 + u.i) <20; and replace the 20 with whatever. (Full disclosure: I ripped off Baron Schwartz integer table from http://www.oreilly.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html ) -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
