Я использую InnoDB.
QUERY, EXPLAIN и INDEXES
SELECT stories.*, count(comments.id) AS comments, GROUP_CONCAT( DISTINCT classifications2.name SEPARATOR ';' ) AS classifications_name, GROUP_CONCAT( DISTINCT images.id ORDER BY images.position, images.id SEPARATOR ';' ) AS images_id, GROUP_CONCAT( DISTINCT images.caption ORDER BY images.position, images.id SEPARATOR ';' ) AS images_caption, GROUP_CONCAT( DISTINCT images.thumbnail ORDER BY images.position, images.id SEPARATOR ';' ) AS images_thumbnail, GROUP_CONCAT( DISTINCT images.medium ORDER BY images.position, images.id SEPARATOR ';' ) AS images_medium, GROUP_CONCAT( DISTINCT images.large ORDER BY images.position, images.id SEPARATOR ';' ) AS images_large, GROUP_CONCAT( DISTINCT users.id ORDER BY users.id SEPARATOR ';' ) AS authors_id, GROUP_CONCAT( DISTINCT users.display_name ORDER BY users.id SEPARATOR ';' ) AS authors_display_name, GROUP_CONCAT( DISTINCT users.url ORDER BY users.id SEPARATOR ';' ) AS authors_url FROM stories LEFT JOIN classifications ON stories.id = classifications.story_id LEFT JOIN classifications AS classifications2 ON stories.id = classifications2.story_id LEFT JOIN comments ON stories.id = comments.story_id LEFT JOIN image_story ON stories.id = image_story.story_id LEFT JOIN images ON images.id = image_story.`image_id` LEFT JOIN author_story ON stories.id = author_story.story_id LEFT JOIN users ON users.id = author_story.author_id WHERE classifications.`name` LIKE 'Home:Top%' AND stories.status = 1 GROUP BY stories.id ORDER BY classifications.`name`, classifications.`position` +----+-------------+------------------+--------+---------------+----------+---------+------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+--------+---------------+----------+---------+------------------------+--------+----------------------------------------------+ | 1 | SIMPLE | stories | ref | status | status | 1 | const | 434792 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | classifications | ref | story_id | story_id | 4 | stories.id | 1 | Using where | | 1 | SIMPLE | classifications2 | ref | story_id | story_id | 4 | stories.id | 1 | Using where | | 1 | SIMPLE | comments | ref | story_id | story_id | 8 | stories.id | 6 | Using where; Using index | | 1 | SIMPLE | image_story | ref | story_id | story_id | 4 | stories.id | 1 | NULL | | 1 | SIMPLE | images | eq_ref | PRIMARY | PRIMARY | 4 | image_story.image_id | 1 | NULL | | 1 | SIMPLE | author_story | ref | story_id | story_id | 4 | stories.id | 1 | Using where | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | author_story.author_id | 1 | Using where | +----+-------------+------------------+--------+---------------+----------+---------+------------------------+--------+----------------------------------------------+ +-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | +-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+ | stories | 0 | PRIMARY | 1 | id | A | 869584 | NULL | NULL | | BTREE | | stories | 1 | created_at | 1 | created_at | A | 434792 | NULL | NULL | | BTREE | | stories | 1 | source | 1 | source | A | 2 | NULL | NULL | YES | BTREE | | stories | 1 | source_id | 1 | source_id | A | 869584 | NULL | NULL | YES | BTREE | | stories | 1 | type | 1 | type | A | 2 | NULL | NULL | | BTREE | | stories | 1 | status | 1 | status | A | 2 | NULL | NULL | | BTREE | | stories | 1 | type_status | 1 | type | A | 2 | NULL | NULL | | BTREE | | stories | 1 | type_status | 2 | status | A | 2 | NULL | NULL | | BTREE | | classifications | 0 | PRIMARY | 1 | id | A | 207 | NULL | NULL | | BTREE | | classifications | 1 | story_id | 1 | story_id | A | 207 | NULL | NULL | | BTREE | | classifications | 1 | name | 1 | name | A | 103 | NULL | NULL | | BTREE | | classifications | 1 | name | 2 | position | A | 207 | NULL | NULL | YES | BTREE | | comments | 0 | PRIMARY | 1 | id | A | 239336 | NULL | NULL | | BTREE | | comments | 1 | status | 1 | status | A | 2 | NULL | NULL | | BTREE | | comments | 1 | date | 1 | date | A | 239336 | NULL | NULL | | BTREE | | comments | 1 | story_id | 1 | story_id | A | 39889 | NULL | NULL | | BTREE | +-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
ВРЕМЯ ЗАПРОСА
В среднем выполняется 0.035 seconds
.
Если я удаляю только GROUP BY
, время падает до 0.007
в среднем.
Если я удаляю только фильтр stories.status=1
, время падает до 0.025
в среднем. Кажется, что это можно легко оптимизировать.
И если я удалю только фильтр LIKE
и ORDER BY
, время в среднем сократится до 0.006
.
ОБНОВЛЕНИЕ 1: 2013-04-13
Мое понимание улучшило многообразие ответов.
Я добавил индексы author_story
и images_story
, которые казались улучшенными запросами до 0.025
секунд, но по какой-то странной причине план EXPLAIN
выглядит намного лучше. На этом этапе удаление ORDER BY
отбрасывает запрос до 0.015
секунд, а при отбрасывании как ORDER BY
, так и GROUP BY
улучшается производительность запросов до 0.006
. Я - это две вещи, на которые нужно сосредоточиться прямо сейчас? Я могу переместить ORDER BY
в логику приложения, если это необходимо.
Вот пересмотренные EXPLAIN
и INDEXES
+----+-------------+------------------+--------+---------------------------------+----------+---------+--------------------------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+--------+---------------------------------+----------+---------+--------------------------+------+--------------------------------------------------------+ | 1 | SIMPLE | classifications | range | story_id,name | name | 102 | NULL | 14 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | stories | eq_ref | PRIMARY,status | PRIMARY | 4 | classifications.story_id | 1 | Using where | | 1 | SIMPLE | classifications2 | ref | story_id | story_id | 4 | stories.id | 1 | Using where | | 1 | SIMPLE | author_story | ref | author_id,story_id,author_story | story_id | 4 | stories.id | 1 | Using index condition | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | author_story.author_id | 1 | Using where | | 1 | SIMPLE | comments | ref | story_id | story_id | 8 | stories.id | 8 | Using where; Using index | | 1 | SIMPLE | image_story | ref | story_id,story_id_2 | story_id | 4 | stories.id | 1 | NULL | | 1 | SIMPLE | images | eq_ref | PRIMARY,position_id | PRIMARY | 4 | image_story.image_id | 1 | NULL | +----+-------------+------------------+--------+---------------------------------+----------+---------+--------------------------+------+--------------------------------------------------------+ +-----------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | author_story | 0 | PRIMARY | 1 | id | A | 220116 | NULL | NULL | | BTREE | | | | author_story | 0 | story_author | 1 | story_id | A | 220116 | NULL | NULL | | BTREE | | | | author_story | 0 | story_author | 2 | author_id | A | 220116 | NULL | NULL | | BTREE | | | | author_story | 1 | author_id | 1 | author_id | A | 2179 | NULL | NULL | | BTREE | | | | author_story | 1 | story_id | 1 | story_id | A | 220116 | NULL | NULL | | BTREE | | | | image_story | 0 | PRIMARY | 1 | id | A | 148902 | NULL | NULL | | BTREE | | | | image_story | 0 | story_image | 1 | story_id | A | 148902 | NULL | NULL | | BTREE | | | | image_story | 0 | story_image | 2 | image_id | A | 148902 | NULL | NULL | | BTREE | | | | image_story | 1 | story_id | 1 | story_id | A | 148902 | NULL | NULL | | BTREE | | | | image_story | 1 | image_id | 1 | image_id | A | 148902 | NULL | NULL | | BTREE | | | | classifications | 0 | PRIMARY | 1 | id | A | 257 | NULL | NULL | | BTREE | | | | classifications | 1 | story_id | 1 | story_id | A | 257 | NULL | NULL | | BTREE | | | | classifications | 1 | name | 1 | name | A | 128 | NULL | NULL | | BTREE | | | | classifications | 1 | name | 2 | position | A | 257 | NULL | NULL | YES | BTREE | | | | stories | 0 | PRIMARY | 1 | id | A | 962570 | NULL | NULL | | BTREE | | | | stories | 1 | created_at | 1 | created_at | A | 481285 | NULL | NULL | | BTREE | | | | stories | 1 | source | 1 | source | A | 4 | NULL | NULL | YES | BTREE | | | | stories | 1 | source_id | 1 | source_id | A | 962570 | NULL | NULL | YES | BTREE | | | | stories | 1 | type | 1 | type | A | 2 | NULL | NULL | | BTREE | | | | stories | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE | | | | stories | 1 | type_status | 1 | type | A | 2 | NULL | NULL | | BTREE | | | | stories | 1 | type_status | 2 | status | A | 6 | NULL | NULL | | BTREE | | | | comments | 0 | PRIMARY | 1 | id | A | 232559 | NULL | NULL | | BTREE | | | | comments | 1 | status | 1 | status | A | 6 | NULL | NULL | | BTREE | | | | comments | 1 | date | 1 | date | A | 232559 | NULL | NULL | | BTREE | | | | comments | 1 | story_id | 1 | story_id | A | 29069 | NULL | NULL | | BTREE | | | | images | 0 | PRIMARY | 1 | id | A | 147206 | NULL | NULL | | BTREE | | | | images | 0 | source_id | 1 | source_id | A | 147206 | NULL | NULL | YES | BTREE | | | | images | 1 | position | 1 | position | A | 4 | NULL | NULL | | BTREE | | | | images | 1 | position_id | 1 | id | A | 147206 | NULL | NULL | | BTREE | | | | images | 1 | position_id | 2 | position | A | 147206 | NULL | NULL | | BTREE | | | | users | 0 | PRIMARY | 1 | id | A | 981 | NULL | NULL | | BTREE | | | | users | 0 | users_email_unique | 1 | email | A | 981 | NULL | NULL | | BTREE | | | +-----------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ SELECT stories.*, count(comments.id) AS comments, GROUP_CONCAT(DISTINCT users.id ORDER BY users.id SEPARATOR ';') AS authors_id, GROUP_CONCAT(DISTINCT users.display_name ORDER BY users.id SEPARATOR ';') AS authors_display_name, GROUP_CONCAT(DISTINCT users.url ORDER BY users.id SEPARATOR ';') AS authors_url, GROUP_CONCAT(DISTINCT classifications2.name SEPARATOR ';') AS classifications_name, GROUP_CONCAT(DISTINCT images.id ORDER BY images.position,images.id SEPARATOR ';') AS images_id, GROUP_CONCAT(DISTINCT images.caption ORDER BY images.position,images.id SEPARATOR ';') AS images_caption, GROUP_CONCAT(DISTINCT images.thumbnail ORDER BY images.position,images.id SEPARATOR ';') AS images_thumbnail, GROUP_CONCAT(DISTINCT images.medium ORDER BY images.position,images.id SEPARATOR ';') AS images_medium, GROUP_CONCAT(DISTINCT images.large ORDER BY images.position,images.id SEPARATOR ';') AS images_large FROM classifications INNER JOIN stories ON stories.id = classifications.story_id LEFT JOIN classifications AS classifications2 ON stories.id = classifications2.story_id LEFT JOIN comments ON stories.id = comments.story_id LEFT JOIN image_story ON stories.id = image_story.story_id LEFT JOIN images ON images.id = image_story.`image_id` INNER JOIN author_story ON stories.id = author_story.story_id INNER JOIN users ON users.id = author_story.author_id WHERE classifications.`name` LIKE 'Home:Top%' AND stories.status = 1 GROUP BY stories.id ORDER BY NULL
ОБНОВЛЕНИЕ 2: 2013-04-14
Я заметил еще одну вещь. Если я не SELECT stories.content
(LONGTEXT) и stories.content_html
(LONGTEXT), запрос падает с 0.015
секунд до 0.006
секунд. На данный момент я рассматриваю вопрос, могу ли я обойтись без content
и content_html
или заменить их чем-то другим.
Я обновил запрос, индексы и объяснил в обновленном обновлении 2013-04-13 вместо повторной публикации в этом, поскольку они были незначительными и инкрементальными. Запрос все еще использует filesort
. Я не могу избавиться от GROUP BY
, но избавился от ORDER BY
.
ОБНОВЛЕНИЕ 3: 2013-04-16
В соответствии с запросом я сбросил индексы history_id из image_story
и author_story
, поскольку они являются избыточными. Результатом было то, что вывод объяснения только изменился, чтобы показать, что параметр possible_keys
изменился. Он до сих пор не показывал оптимизацию Using Index
.
Также изменен LONGTEXT
на TEXT
и теперь выберем LEFT(stories.content, 500)
вместо stories.content
, что делает очень значительную разницу в времени выполнения запроса.
+----+-------------+------------------+--------+-----------------------------+--------------+---------+--------------------------+------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+--------+-----------------------------+--------------+---------+--------------------------+------+---------------------------------------------------------------------+ | 1 | SIMPLE | classifications | ref | story_id,name,name_position | name | 102 | const | 10 | Using index condition; Using where; Using temporary; Using filesort | | 1 | SIMPLE | stories | eq_ref | PRIMARY,status | PRIMARY | 4 | classifications.story_id | 1 | Using where | | 1 | SIMPLE | classifications2 | ref | story_id | story_id | 4 | stories.id | 1 | Using where | | 1 | SIMPLE | author_story | ref | story_author | story_author | 4 | stories.id | 1 | Using where; Using index | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 4 | author_story.author_id | 1 | Using where | | 1 | SIMPLE | comments | ref | story_id | story_id | 8 | stories.id | 8 | Using where; Using index | | 1 | SIMPLE | image_story | ref | story_image | story_image | 4 | stories.id | 1 | Using index | | 1 | SIMPLE | images | eq_ref | PRIMARY,position_id | PRIMARY | 4 | image_story.image_id | 1 | NULL | +----+-------------+------------------+--------+-----------------------------+--------------+---------+--------------------------+------+---------------------------------------------------------------------+ innodb_buffer_pool_size 134217728 TABLE_NAME INDEX_LENGTH image_story 10010624 image_story 4556800 image_story 0 TABLE_NAME INDEX_NAMES SIZE dawn/image_story story_image 13921