Оптимизация SQL-запросов при использовании нескольких соединений и большого набора записей

Question

Я создаю доску объявлений и пытаюсь извлечь обычные темы (т. Е. Темы, которые не прикреплены) и отсортировать их по дате последнего опубликованного сообщения. Я могу это сделать, однако, когда у меня есть около 10 000 сообщений и 1500 тем, время запроса>60 секунд.

У меня вопрос: могу ли я что-то сделать с моим запросом, чтобы повысить производительность, или мой дизайн в корне неверен?

Вот запрос, который я использую.

SELECT Messages.topic_id,
       Messages.posted,
       Topics.title,
       Topics.user_id,
       Users.username
FROM Messages
LEFT JOIN
  Topics USING(topic_id)
LEFT JOIN
   Users on Users.user_id = Topics.user_id
WHERE Messages.message_id IN (
    SELECT MAX(message_id)
    FROM Messages
    GROUP BY topic_id)
AND Messages.topic_id
NOT IN (
    SELECT topic_id
    FROM StickiedTopics)
AND Messages.posted IN (                           
    SELECT MIN(posted)
    FROM Messages 
    GROUP BY message_id)
AND Topics.board_id=1
ORDER BY Messages.posted DESC LIMIT 50

Редактировать Вот план объяснения

+----+--------------------+----------------+----------------+------------------+----------+---------+-------------------------+------+----------------------------------------------+
| id | select_type        | table          | type           | possible_keys    | key      | key_len | ref                     | rows | Extra                                        |
+----+--------------------+----------------+----------------+------------------+----------+---------+-------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | Topics         | ref            | PRIMARY,board_id | board_id | 4       | const                   |  641 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | Users          | eq_ref         | PRIMARY          | PRIMARY  | 4       | spergs3.Topics.user_id  |    1 |                                               |
|  1 | PRIMARY            | Messages       | ref            | topic_id         | topic_id | 4       | spergs3.Topics.topic_id |    3 | Using where                                   |
|  4 | DEPENDENT SUBQUERY | Messages       | index          | NULL             | PRIMARY  | 8       | NULL                    |    1 |                                              |
|  3 | DEPENDENT SUBQUERY | StickiedTopics | index_subquery | topic_id         | topic_id | 4       | func                    |    1 | Using index                                  |
|  2 | DEPENDENT SUBQUERY | Messages       | index          | NULL             | topic_id | 4       | NULL                    |    3 | Using index                                  |
+----+--------------------+----------------+----------------+------------------+----------+---------+-------------------------+------+----------------------------------------------+

Индексы

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Messages |          0 | PRIMARY  |            1 | message_id  | A         |        9956 |     NULL | NULL   |      | BTREE      |         |
| Messages |          0 | PRIMARY  |            2 | revision_no | A         |        9956 |     NULL | NULL   |      | BTREE      |         |
| Messages |          1 | user_id  |            1 | user_id     | A         |         432 |     NULL | NULL   |      | BTREE      |         |
| Messages |          1 | topic_id |            1 | topic_id    | A         |        3318 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Topics |          0 | PRIMARY  |            1 | topic_id    | A         |        1205 |     NULL | NULL   |      | BTREE      |         |
| Topics |          1 | user_id  |            1 | user_id     | A         |         133 |     NULL | NULL   |      | BTREE      |         |
| Topics |          1 | board_id |            1 | board_id    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Users |          0 | PRIMARY         |            1 | user_id     | A         |        2051 |     NULL | NULL   |      | BTREE      |         |
| Users |          0 | username_UNIQUE |            1 | username    | A         |        2051 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

Я хотел бы начать с первой основы квалифицированных тем, получить эти идентификаторы, а затем присоединиться. Мой внутренний первый запрос выполняет предварительную квалификацию, сгруппированную по topic_id и сообщению max, чтобы получить предварительно определенные квалифицированные идентификаторы. Я также применил левое соединение к stickiesTopics тоже. Зачем? Делая левое соединение, я могу искать тех, кто НАЙДЕН (тех, кого вы хотите исключить). Поэтому я применил предложение WHERE для идентификатора темы Stickies NULL (то есть: НЕ найден). Таким образом, сделав это, мы УЖЕ спарили список по списку ЗНАЧИТЕЛЬНО, не выполнив несколько вложенных подзапросов. Из этого результата мы можем присоединиться к сообщениям, темам (включая квалификатор board_id = 1), пользователям и получать детали по мере необходимости. Наконец, примените один суб-выбор WHERE IN для своего MIN(опубликованного) квалификатора. Не понимаю основы этого, но оставил это как часть вашего исходного запроса. Тогда порядок по лимиту.

SELECT STRAIGHT_JOIN
      M.topic_id,
      M.posted,
      T.title,
      T.user_id,
      U.username
   FROM 
      ( select 
              M1.Topic_ID, 
              MAX( M1.Message_id ) MaxMsgPerTopic
           from 
              Messages M1
                 LEFT Join StickiedTopics ST
                    ON M1.Topic_ID = ST.Topic_ID
           where
              ST.Topic_ID IS NULL
           group by 
              M1.Topic_ID ) PreQuery
        JOIN Messages M
           ON PreQuery.MaxMsgPerTopic = M.Message_ID
           JOIN Topics T
               ON M.Topic_ID = T.Topic_ID
              AND T.Board_ID = 1
              LEFT JOIN Users U
                 on T.User_ID = U.user_id 
   WHERE
      M.posted IN ( SELECT MIN(posted)
                       FROM Messages 
                       GROUP BY message_id)
   ORDER BY 
      M.posted DESC 
   LIMIT 50

Answer 2

Я предполагаю, что большая часть вашей проблемы лежит в ваших подзапросах. Попробуйте что-то вроде этого:

SELECT Messages.topic_id,
       Messages.posted,
       Topics.title,
       Topics.user_id,
       Users.username
FROM Messages
LEFT JOIN
    Topics USING(topic_id)
LEFT JOIN
    StickiedTopics ON StickiedTopics.topic_id = Topics.topic_id 
                   AND StickedTopics.topic_id IS NULL
LEFT JOIN
    Users on Users.user_id = Topics.user_id
WHERE Messages.message_id IN (
    SELECT MAX(message_id)
    FROM Messages m1
    WHERE m1.topic_id = Messages.topic_id)
AND Messages.posted IN (                           
    SELECT MIN(posted)                                                                                           
    FROM Messages m2
    GROUP BY message_id)
AND Topics.board_id=1
ORDER BY Messages.posted DESC LIMIT 50

Я оптимизировал первый подзапрос, удалив группировку. Второй подзапрос был ненужным, потому что его можно заменить на JOIN,

Я не совсем уверен, что должен делать этот третий подзапрос:

AND Messages.posted IN (                           
    SELECT MIN(posted)                                                                                           
    FROM Messages m2
    GROUP BY message_id)

Я мог бы помочь оптимизировать это, если бы я знал, что он должен делать. Что именно posted - дата, целое число и т. д.? Что это представляет?