Big Mysql data messages, taking too long to load

user_8246112 0
Viewed 66

Im using MySQL as Database and fetching messages via PHP, so my problem is i don't know much MySQL, and my query is taking to long to fetch the messages, my messages table has 731,847 Rows

My query:

    SELECT DISTINCT
    c.from_id,
    c.to_id
FROM
    messages c
JOIN(
    SELECT CASE WHEN
        `from_id` = '15' THEN `to_id` ELSE `from_id`
END AS other,
MAX(TIME) AS latest
FROM
    messages
WHERE
    `from_id` = '15' OR `to_id` = '15'
GROUP BY
    other
) m
ON
    (
        c.from_id = '15' AND c.to_id = m.other OR c.to_id = '15' AND c.from_id = m.other
    ) AND c.time = m.latest
ORDER BY
    TIME
DESC

Showing rows 0 - 24 (27289 total, Query took 1.8380 seconds.)

Is there any way to improve this query, and load it faster.

mysql
1 Answers
SHOW CREATE TABLE messages;

If you do not have an index on your TIME field, you should add one.

Also, any additional filtering you can add via WHERE clause to reduce the number of records that are brought back is going to help it finish faster.

Once you've added the index on the time field, or if it already has one, you can append the "SHOW " keyword in front of your query and it will show you how many rows are being scanned and whether indexes are being used or not.

user_33794460
Related