Hello, there is a need to make personal messages. I introduced how to do it, I just think: let's say there is a table of messages, there are subjectTO fields, subjectMe, text, date, it seems to me that this is not the right way to do, let's say there are 20,000 messages, it will take them a long time to go there until the cycle goes through them will be a large number of time, and if a million? advise what and how better, I will be very grateful!

  • What cycle will run? Index the fields. - Oleg

2 answers 2

In the messages table, you must have the following fields:

  • sender_id - sender id
  • receiver_id - receiver ID
  • message - the message itself
  • created - creation date

Accordingly, the sent messages can be selected:

SELECT * FROM messages where sender_id=<идентификатор отправителя>

ZY in fact, this is not the most optimal (in terms of speed) structure, but I don’t think if you ask such questions you don’t need this optimization. Premature optimization is evil.

    Table messages:

    • sender_id - sender
    • receiver_id - receiver
    • message - message text
    • status - read / not read
    • datetime - creation date
    • List item

    And the actual sample:

     SELECT COUNT(*) FROM messages WHERE receiver_id=<идентификатор получателя> and status='unread'

    We look if there are messages addressed to the current user and that have not been read.