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
Hope this helps. Velen ----- Original Message ----- From: "Nacho Garcia" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, March 16, 2008 1:42 AM Subject: how to use index with order by here > 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. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
