hello, im trying to make a DB for a message system. the best way i have made is this:
*TABLE conversations * (informacion de cada conversacion) ................................. i*d_conversation (bigint) count (smallint) updated every time a new message is added to this conversation lastmessagetime (timestamp) **updated every time a new message is added to this conversation** * *INDEX (id_conversation,lastmessage)** * * **TABLE user_conversations* (relacion usuario-conversacion) .......................................... *id_user (int) id_conversation (bigint) **read (bool) ** **INDEX (id_conversation) INDEX (id_user)* *TABLE messages * (mensajes on every conversation) ........................... id_conversation (bigint) id_writer (int) message (varchar) time (timestamp) INDEX (id_conversation,time) my problem is that i need to query those things and i dont know how to do that. "*Conversation list of a given user with `read`, number of messages (`count`) , date of last message (i store this value on `conversation` table) and who did it Order by time of the last message of each conversation*" i have tried everything i could but im not getting a good solution: SELECT UC.id_conversation, UC.read, C.count, UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( lastmessage ) , M.message, M.id_usr FROM user_conversations UC, conversations C, messages M WHERE UC.id_usr = 1 AND C.id_conversation = UC.id_conversation AND M.id_conversation = UC.id_conversation AND M.time = C.lastmessage ORDER BY C.lastmessage DESC LIMIT 0,10 thats giving me an ALL in `conversations` as result id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE UC ref id_conversation,id_usr id_usr 4 const 3 Using temporary; Using filesort 1 SIMPLE M ref orderbytime orderbytime 8 netlivin3.UC.id_conversation 1 1 SIMPLE C ALL id_conversation NULL NULL NULL 3 Using where im completely lost here, ill really appreciate any help. thanks.