From: Nacho Garcia (nachoabgmail.com)
Date: Thu Mar 06 2008 - 18:37:22 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi, im having a lot of trouble with one query, i hope someone could give me
a hand with this, i would be really grateful

these are the tables:

TABLE friends
id_usr INT
id_friend INT
with INDEX on (id_usr,id_friend)

TABLE status
id_usr INT
lastConnection
.... (other irrelevant info)
with INDEX on (id_usr,lastConnection)

im trying to get friends of a given id_usr ordered by lastConnection.

i tried a lot, the simplest query give me the best results

explain SELECT F.id_friend,S.lastConnection
FROM friends F, user_status S
WHERE F.id_friend = S.id_usr
AND F.id_usr = 1
ORDER BY lastConnection

id select_type table type possible_keys key key_len ref rows Extra 1
SIMPLE F ref id_usr id_usr 4 const 20 Using temporary; Using filesort 1
SIMPLE S ref id_usr id_usr 4 netlivin3.F.id_friend 3 Using index
it's really bad, but at least it's ref type so only read the rows of friends
table matching id_usr=1

**

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]