Rob Wultsch schrieb:
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.
hu? ... i am pretty sure i had a similar problem, i solved this by adding
the column with the order to the index, which 'solved' the filesort (except
the ORDER is in reverse)
The filesort will not be any sort of a problem unless
the result is large.
yes. of course, size matters, if your whole DB is small enough you will not
even get any performance impacts without any index ... ;-)
but it requires more resources, or?
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
