Search code examples
phpmysqlquery-optimization

Optimizing MySQL database for select statements when there are 1M records: store old data separately?

I have a website where users maintain conversations with each other. Each comment is stored in a MySQL database. DB has a table named comment with a foreign key that refers to conversation. Messages of a conversation are returned to client by steps, limiting rows with a "limit" clause (this is important for my question). For instance,

SELECT * FROM `messages` WHERE `user_to_id` = 1 and `conversation_id` = 4 limit 10,5

My question is next: if there were too many records in comments table, selects queries like above would be slower?
If it is, is a good practice store old records in a separate table or in a server file in order to speed up select queries only with recent comments?

Solution

  • If the query SELECT * FROM `messages` WHERE `user_to_id` = 1 and `conversation_id` = 4 limit 10,5 already returns the most recent messages, then your limit clause ensures speediness, there should be no issue with keeping millions of messages as you are limiting the resulting dataset.