php - How to speed up this MySQL query? Latest messages query -

php - How to speed up this MySQL query? Latest messages query -

i need speed query list latest messages. query running long (eg. 10 seconds ...)

select datas.uid, datas.message, datas.date, concat(conv.first_name, ' ', conv.last_name) conversation_name (select m.message_id, m.message, if (m.from_uid = 1, m.to_uid, m.from_uid) uid, m.readed, m.sended `date` users u left bring together messages m on m.from_uid = u.user_id m.message_id in (select max(message_id) messages to_uid = 1 or from_uid = 1 grouping least(from_uid, to_uid), greatest(from_uid, to_uid))) datas left bring together users conv on conv.user_id = datas.uid order datas.date desc limit 5

this query utilize 2 tables (users , messages). table users:

user_id (primary, autoincrement) login pass first_name last_name ....

table messages:

message_id (primary, autoincrement) from_uid (sender message, reference table users -> user_id) to_uid (receiver message, reference table users -> user_id) sended (timestamp) message (varchar)

edit added indexes messages: - from_uid - to_uid - sended

and without efect...

try creating indexed on id's you're checking.

in case might want create index on: conv.user_id, datas.uid, m.message_id, messages.to_uid, messages.from_uid, , datas.date might thought well, since you're sorting on that.

php mysql performance query-optimization