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_fromint(10)
id_toint(10)
textvarchar(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]
