Friends, I read several posts about using index in MySQL database tables, their advantages and disadvantages, but I still had a question for my situation.
Consider over 300,000 registered users and they can talk to each other when one adds the other as a friend and then talks via the exchange of messages. The table that stores the messages will have millions of records and needs to be read every time a user enters the chat screen of this user with another user (retrieving the messages exchanged between them previously).
The table was mounted like this: idmsg, idusuario1, idusuario2, textomsg, datahoramsg
Would it be worth indexing the id1u1 and id1u2 fields?
The problem: The table would receive new inserts of exchanged messages at any time, including with id of new registered users that would change messages as well. Does not that mean that indexing would be updated at all times, so it's a bad idea to use it? Alias, here is another doubt ... is the indexing updated with any addition of record or only if it has 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 around this is whether any update affects indexing or only if it is an update in the indexed field. Example, the user makes an edit in his message. It will be searched in the middle of millions of messages in the table and will edit the textomsg field. Does this affect indexing in the id1u1 and id2u2 fields?
Thanks for the help! Thanks!