Rob Wultsch schrieb:
On Sat, Mar 15, 2008 at 2:42 PM, Nacho Garcia <[EMAIL PROTECTED]> wrote:
Hi, im having troubles with one query, hope someone can help.
on this table:
messages:
id_from int(10)
id_to int(10)
text varchar(1000)
time
with index on id_form and id_to
i want to get messages sent from one user to another and vice versa order by
time.
let say we want to search messages between user 1 and 2:
i'm doing:
SELECT *
FROM messages
WHERE id_from in (1,2)
AND id_to in (1,2)
ORDER BY time
but that gives me a filesort in all rows matching the where clause, and
thats not good. I tried by indexing id_from, id_to, time but thats not
working of course.
any help would be really appreciate.
Short answer: The filesort is not necessary not your problem. Add a
composite key on id_from,id_to.
ALTER TABLE `messages` ADD INDEX ( `id_from` , `id_to` )
this will not prevent filesort, because the results still needs to be sorted
for ORDER BY time, or?
i would try INDEX(`id_from`, `id_to`, `time`)
but i am not sure if this will help, cause of this two IN()
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
