1
Friends, I read several posts about the use of the index in the tables of the Mysql database, its advantages and disadvantages, but I still had a question for my situation.
Consider over 300,000 registered users and they can chat with each other when one adds the other as a friend and then chat via message exchange. The table that stores the messages will stay with millions of records and needs to be read every time a user enters this user’s chat screen with another user (retrieving the messages exchanged between them previously).
The table was mounted like this: idmsg, idusuario1, idusuario2, textomsg, datahoramsg
It would be worth indexing the idusuario1 and idusuario2 fields?
The problem: The table would receive all the time new insertions of exchanged messages, including id of new registered users who would exchange messages as well. Doesn’t that mean that the indexing would be updated at any time, so it would be a bad idea to use it? Alias, here is another question... the indexing is updated with any addition of record or only if you have a value never seen in the indexed field (the id of a new registered user who just sent his first message, for example)?
Another question surrounding this is whether any update affects an index or only if it is an update in the indexed field. Example, the user makes an edit on your message. It will be searched amid millions of messages in the table and will edit the textomsg field. This affects indexing in idusuario1 and idusuario2?
I appreciate the help! Thank you!