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` ) Long answer http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html "In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: ... The key used to fetch the rows is not the same as the one used in the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1;" As a quick example using your table I will generate a bunch of data to play with: DROP TABLES IF EXISTS messages,integers,user_ids; CREATE TABLE integers(i int NOT NULL PRIMARY KEY); INSERT INTO integers(i) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE user_ids(`id` int(10)) SELECT t.i * 10 + u.i as id FROM integers AS u, integers as t WHERE (t.i * 10 + u.i) < 100; CREATE TABLE `messages` ( `id_from` int(10) NOT NULL, `id_to` int(10) NOT NULL, `text` varchar(1000) NOT NULL, `time` datetime NOT NULL ); INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',NOW() FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 1 DAY) FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 2 DAY) FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 3 DAY) FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 5 DAY) FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 6 DAY) FROM user_ids t1, user_ids t2; INSERT INTO messages(id_from, id_to,text,time) SELECT t1.id, t2.id, '',DATE_SUB(NOW( ), INTERVAL 7 DAY) FROM user_ids t1, user_ids t2; This creates 70k rows. Without an index your query takes ~.25 sec on a rather slow box and examines 70k rows. With an index on id_from your query takes ~.01 sec and examines 1.4k rows. With a composite index on id_from,id_to your query takes ~.002 sec and examines 28 rows, and has a filesort. Still pretty fast... -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
