CODEWITHSUNDEEP
mysqlsql
eXistenZ
eXistenZ

Reputation: 336

Group last messages with a better MySQL query

I'm trying to show a list of all last messages, similar to how you see them in Facebook when you go to see all messages (and show who messaged last and the message).

My table looks like this:

id | userid | fromuserid | touserid | message | time
 1 |   1    |     1      |    2     |  test1  | 1428882596
 2 |   2    |     1      |    2     |  test1  | 1428882596
 3 |   2    |     2      |    1     |  test2  | 1428882600
 4 |   1    |     2      |    1     |  test2  | 1428882600

userid is there because the messages are duplicated (each user has his/her own copy of the message, so they can delete them and also because they have a limit of maximum messages they can store).

And this is my query:

   SELECT messages.*
        , users.username
        , users.firstname
        , users.lastname
     FROM messages
LEFT JOIN users ON ( users.id            = messages.fromuserid
                     AND messages.touserid   = '$userid' )
                OR ( users.id            = messages.touserid
                     AND messages.fromuserid = '$userid' )
    WHERE messages.id IN ( SELECT MAX(id)
                             FROM messages
                            WHERE userid = '$userid'
                            GROUP BY GREATEST(fromuserid, touserid)
                                     , LEAST(fromuserid, touserid) )
    ORDER BY messages.time DESC

The query works, but it is very slow (about 0.1 - 0.2 seconds to execute, the table has around 8000 entries). I do have indexes set too, it's just the query that is slow.

Does anyone know a better way to do this without creating an extra table to store the last messages? No matter how much I try I always end up with the idea that I need to create an extra table, which I would like to avoid if possible.

Edit: I know the text duplication is a bad idea (I will create a separate table for that), my question is just for the grouping and showing the last messages, if it can be done without an extra table just for that.

Thanks a lot!

Upvotes: 1

Views: 87

Answers (2)

Brian Patterson
Brian Patterson

Reputation: 1625

you want as many tables as possible (within reason).

first rule, relate, dont duplicate!

also index the columns if you can.

CREATE FULLTEXT INDEX idx ON `messages`(`message`);

FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns, omitting any words that are defined as stopwords.

A FULLTEXT index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX.

https://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html

Upvotes: 0

4givN
4givN

Reputation: 3234

If you care about performance, sometimes it would be better to handle complex query using flag (extra column we add in our table)

1- add a column in your message table, name it isLast, put default value as 1

2- create a trigger before insert to update every old row in message which belong to the current user (I assume you can get it with NEW.touserid or NEW.fromuserid), SET isLast = 0

3-Then when joining message with users, you just need to check isLast > 0 in the condition (no more subquery)

Upvotes: 2

Related Questions