0

i'm thinking about something. Consider a table "messages" which stores messages between member. This table can potentially billions messages. Looking for all message from a message would become a long query (in term of time) is it a good idea to put last message in a dedicated table ? So each entry in the table 'last_message' looks like

id | msg | user_pk

This table is updated each time a user add a new message, the previsous one goes to the table "message" So each time we look for all last message we just have to look into the last_message table which contains less data than "messages"

To me the main advantage is the request speed, the disadvantage is the data management bewteen "messages" and "last_messages" tables and update requests.

  • Having less data doesn't necessarily mean it will be significantly faster. You have to think about all of the extra reading and writing that your idea would involve. A properly indexed read is extremely fast, even in huge tables. It is a mistake to assume poor performance without actually testing it out. – Joel Brown Jan 10 '14 at 12:04
1

It is likely that you would partition the table by time or region, etc. Also this is what indexes or caching already do.

So your idea could be appropriate but you need to think about whether you are reinventing the wheel or if your use case can really benefit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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