mysql> desc posts_posts;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)    | NO   | MUL | NULL    |                |
| body       | text       | YES  |     | NULL    |                |
| created_at | datetime   | YES  |     | NULL    |                |
| updated_at | datetime   | YES  |     | NULL    |                |
| is_deleted | tinyint(1) | NO   |     | 0       |                |
+------------+------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)

mysql> desc posts_personas_assc;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| post_id    | int(11)  | NO   | MUL | NULL    |                |
| persona_id | int(11)  | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Essentially, user produces posts. Each publish might have "personas" mounted on them. Quite simply, it is a many-to-many relationship. (There's another persona table which isn't proven here)

Suppose I've 50 million documents approximately. I wish to run this question:

SELECT pp.id FROM posts_posts pp 
 INNER JOIN posts_persona_assc ppa ON pp.id = ppa.post_id
 WHERE ppa.persona_id IN(id1, id2, id3)
   AND start_time > pp.created_at
 ORDER BY created_at DESC LIMIT num;
  • I'll have ppa.persona_id and pp.produced_at indexed, obviously
  • the "IN" portion won't ever convey more than 3 IDS.

Could it be efficient enough that i can run this question? Could it be fast enough?

This question wil be went each time someone hits a person profile page.

Option to IN clause might provide you with faster result, take a look at IN variation at IN Slowness