0

I am in the process of building a messaging section for my website where clients and admin can send each other messages. I am using mysql. I am just curious, My database contains a table structured with the following columns:

(message, recipient, sender and time send)

Every time a message is sent it creates a new row in the database, I would like to know if there is a more efficient way to do this. Because when the user logs into their account the database will have to query the conversation table. I have it so it only shows the last 10. However, this will slow down when there are more messages in this table.

I am willing to answer any questions you have in the comments.

2 Answers 2

0

You can create an index on a combination of the fields (probably recipient, sender, and time send) in order to speed up lookups on the conversation table. Ordering the index (ascending vs. descending) may also help if you're only interested in returning the ten most recent, relevant messages. Here's one thread on what ordering an index does, here's another about whether it matters to order a date index in ascending or descending order. EDIT: Please see Rick James's comment - apparently index ordering by ASC/DESC is irrelevant for MySQL.

If you are unfamiliar with database indexing, this StackOverflow thread has a good overview of what it is, what it does, why it's used, and when it should be used.

Here's the reference page for creating indexes in MySql 5.7.

2
  • The links on ordering in an index definition is irrelevant in MySQL because that attribute (ASC vs DESC) is ignored by MySQL; both links are to other products.
    – Rick James
    Commented Nov 16, 2016 at 1:03
  • Thanks Rick. I've edited the response to reflect this information. Commented Nov 16, 2016 at 2:25
1

Assuming you want that last 10 messages to the logged in user, regardless of sender, this is optimal:

INDEX(recipient, time_send)

Then use

SELECT ...
    WHERE recipient = <userid>
    ORDER BY time_send DESC;

It will be sufficiently efficient. It can be made slightly more efficient by having

PRIMARY KEY (recipient, time_send, ...)

where the ... is whatever it takes to make the PK unique (a requirement in MySQL).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.