Что случилось с этим запросом? ОБЪЯСНИТЕ выглядит хорошо мне

Попробуйте это и посмотрите, как это делает:

SELECT i.*, s.*, k.*, a.*
FROM items AS i
 JOIN sources AS s ON (i.source_id = s.id)
 JOIN authors AS a ON (i.author_id = a.id)
 JOIN items_keywords AS ik ON (i.id = ik.item_id)
 JOIN keywords AS k ON (k.id = ik.keyword_id)
WHERE k.id IN (SELECT kp.keyword_id
           FROM keywords_profiles AS kp
           WHERE kp.profile_id IN (17))
ORDER BY i.timestamp DESC, i.id DESC
LIMIT 0, 20;

Я факторизовал несколько соединений в несвязанный подзапрос, таким образом, Вы не должны будете делать a GROUP BY отобразить результат на отличные строки.

На самом деле можно все еще получить несколько строк на i.id в моем примере, в зависимости от того, на сколько ключевые слова отображаются на данный объект и также profile_id 17.

filesort сообщаемый в Вашем ОБЪЯСНЯТЬ отчет происходит, вероятно, из-за комбинации GROUP BY и ORDER BY использование различных полей.

Я соглашаюсь с ответ ʞɔıu, что ускорение, вероятно, из-за ключевого кэширования.

Я предположил бы, что Вы выполняете профилировщика на запросе, затем Вы видите, сколько времени каждый подзапрос взял и где время используется. Если у Вас есть phpmyadmin, это - простой флажок, который необходимо проверить для получения этой функциональности, но мое предположение - Вы, может получить его вручную из mysql терминального приложения также. Я не видел, что это объясняет вещь прежде, если это - на самом деле профилирование, я привык к в phpmyadmin, я приношу извинения за ерунду.

Это смотрит хорошо, каждая строка в explain использует какой-то индекс. Одно возможное беспокойство filesort бит. Попытайтесь выполнить запрос без order by пункт и видит, улучшает ли это его.

Затем что я сделал бы, постепенно вынимают каждого join пока Вы (надо надеяться), не получаете то крупное увеличение скорости, затем концентрируетесь на том, почему это происходит.

Причина я упоминаю filesort то, потому что я не вижу упоминание о метке времени нигде в explain вывод (даже при том, что это - Ваши основные критерии сортировки) - это могло бы требовать полного неиндексируемого вида.

UPDATE#1:

На основе edit#2, запроса:

SELECT i.id
    FROM items AS i
    ORDER BY i.timestamp DESC, i.id DESC
    LIMIT 0, 20;

занимает 5-6 секунд. Это отвратительно. Попытайтесь создать сводный индекс на обоих TIMESTAMP и ID и посмотрите, улучшает ли это его:

create index timestamp_id on items(timestamp,id);
select id from items order by timestamp desc,id desc limit 0,20;
select id from items order by timestamp,id limit 0,20;
select id from items order by timestamp desc,id desc;
select id from items order by timestamp,id;

На одном из тестов я бросил убывающий бит (DB2 для, каждый иногда не использует индексы, если они находятся в противоположном порядке). Другое изменение должно снять предел в случае, если это влияет на него.

Чего достигает пункт GROUP BY? Нет никаких агрегатных функций в ВЫБОРЕ, таким образом, GROUP BY должен быть ненужным

Чтобы Ваш запрос работал быстро, Вам нужно:

1. Создайте индекс: CREATE INDEX ix_timestamp_id ON items (timestamp, id)

   • Гарантируйте это idна sources, authors и keywords первичные ключи.

   • MySQL силы, чтобы использовать этот индекс для объектов и работать NESTED LOOP соединения для других объектов:

     ОБЪЯСНИТЕ РАСШИРЕННЫЙ ВЫБОР Item.*, Source . * , Keyword . * , Author . * ОТ items AS Item ВЫЗОВИТЕ ИНДЕКС ДЛЯ ORDER BY (ix_timestamp_id) СОЕДИНЕНИЕ items_keywords AS ItemsKeyword ВЫЗОВИТЕ ИНДЕКС (ix_item_keyword) НА ( Item.id = ItemsKeyword.item_id И ItemsKeyword.keyword_id В (ВЫБОР keyword_id ОТ keywords_profiles AS KeywordsProfile ВЫЗОВИТЕ ИНДЕКС (ix_keyword_profile) ГДЕ KeywordsProfile.profile_id = 17)) СОЕДИНЕНИЕ sources AS Source ВЫЗОВИТЕ ИНДЕКС (основной) НА ( Item.source_id = Source.id ) СОЕДИНЕНИЕ authors AS Author ВЫЗОВИТЕ ИНДЕКС (основной) НА ( Item.author_id = Author.id ) СОЕДИНЕНИЕ keywords AS Keyword ВЫЗОВИТЕ ИНДЕКС (основной) НА ( Keyword.id = ItemsKeyword.keyword_id ) ORDER BY Item.timestamp DESC, Item.id DESC ОГРАНИЧИВАЮТ 0, 20

Как Вы видите, мы избавляемся от GROUP BY, продвиньте подзапрос в JOIN условие и сила PRIMARY KEYs, чтобы использоваться для соединений.

Это - то, как мы гарантируем это NESTED LOOPS с items как ведущие таблицы будут использоваться для всех соединений.

В результате:

1, 'PRIMARY', 'Item',         'index',  '', 'ix_timestamp_id', '12', '', 20, 2622845.00, ''
1, 'PRIMARY', 'Author',       'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.author_id', 1, 100.00, ''
1, 'PRIMARY', 'Source',       'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.source_id', 1, 100.00, ''
1, 'PRIMARY', 'ItemsKeyword', 'ref', 'PRIMARY', 'PRIMARY', '4', 'test.Item.id', 1, 100.00, 'Using where; Using index'
1, 'PRIMARY', 'Keyword',      'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'test.ItemsKeyword.keyword_id', 1, 100.00, ''
2, 'DEPENDENT SUBQUERY',      'KeywordsProfile', 'unique_subquery', 'PRIMARY', 'PRIMARY', '8', 'func,const', 1, 100.00, 'Using index; Using where'

, и когда мы выполняем это, мы добираемся

20 rows fetched in 0,0038s (0,0019s)

Существуют 500k в items, 600k в items_keywords, 512 значения в keywords и 512 значения в keywords_profiles (все с профилем 17).

Некоторые вещи попробовать:

1. Попытайтесь не выбрать все столбцы из всех таблиц и выберите только, в чем Вы нуждаетесь. Это может устранить использование покрытия индексов (ищущий использование индекса в дополнительном столбце) и в целом впитает много бесполезных IO.
2. Это filesort смотрит немного беспокойства. Попытайтесь удалить порядок и заменить его порядком пустым указателем - группа неявно видами в mysql, таким образом, необходимо заказать пустым указателем для удаления того неявного вида.
3. Попытайтесь добавить индекс на объекте (метка времени, идентификатор) или (идентификатор, метка времени). Мог бы сделать что-то об этом filesort (Вы никогда не знаете).
4. Почему Вы группируетесь идентификатором объекта? и не выбирая совокупных столбцов? если Вы сгруппируетесь столбцом и затем выберете (намного меньше порядка) некоторые другие несовокупные столбцы затем, то значения тех столбцов будут выбраны более или менее произвольные. Если, не идентификатор объекта, всегда уникально для этого запроса, в этом случае группа ничего не выполнит.
5. Наконец, по моему опыту, mysql иногда будет просто необъяснимо волноваться, если Вы дадите его, слишком многие соединяют, чтобы попытаться оптимизировать. Попытайтесь выяснить, существует ли некоторый способ, которым Вы не должны делать такого количества соединений все однажды как этот, т.е. разделять его на несколько запросов, если Вы можете.
6. одна из вещей, которая делает это действительно печальным, - то, что, если я неоднократно выполняю этот запрос, требуется меньше чем.1 секунд. Я предполагаю, что это происходит из-за кэша запроса — добавляют SQL_NO_CACHE после ИЗБРАННОГО ключевого слова для отключения использования кэша запроса на этот запрос
7. Всеми таблицами является MyISAM... делает это указывает, что MySQL загружает материал в память, и вот почему это работает настолько быстрее некоторое время — MyISAM использует ключевое буферное, и только кэши индексируют данные в памяти, и полагается на ОС к, надо надеяться, неиндексным данным кэша. В отличие от Innodb, который кэширует все в пуле буферов.

Действительно ли возможно, что у Вас есть проблемы из-за файловой системы ввод-вывод? ОБЪЯСНИТЬ шоу, что должно быть 1 544 строки, выбранные от ItemsKeyword таблица. Если необходимо перейти к диску для каждого из тех, Вы добавите приблизительно 10-15 вторых общих количеств ко времени выполнения (принимающий время поиска высокого выхода, потому что Вы находитесь на VM). Обычно таблицы кэшируются в RAM, или данные хранятся достаточно близко на диске, который могут быть объединены чтения. Однако Вы работаете на VM с 256 МБ поршня, таким образом, Вы можете, никакая память сэкономить его не может кэшироваться в и если Ваш файл таблицы фрагментируется достаточно, Вы смогли добираться, производительность запросов ухудшила это очень.

Вы могли, вероятно, получить некоторое представление о том, что происходит с вводом-выводом во время запроса путем выполнения чего-то как pidstat -d 1 или iostat 1 в другой оболочке на сервере.

Править: От рассмотрения запроса, включающего индекс (ItemsKeyword.item_id, ItemsKeyword.keyword_id) должен зафиксировать его, если моя теория права относительно этого являющийся проблемой с исканием для ItemsKeyword таблица.

MySQL загружается много в различные кэши, включая индексы и запросы. Кроме того, Ваша операционная система сохранит кэш файловой системы, который мог ускорить Ваш запрос при повторном выполнении.

Одна вещь рассмотреть состоит в том, как MySQL составляет временные таблицы во время этого типа запроса. Как Вы видите в Вашем объяснять, временная таблица составляется, вероятно, для сортировки результатов. Обычно MySQL составит эти временные таблицы в памяти, за исключением 2 условий. Во-первых, если они превышают максимальный набор размера в настройках MySQL (макс. временный размер таблицы, или размер "кучи" - проверяют mysqlperformanceblogs.com на большее количество информации об этих настройках). Второй и более важный - это:

• Временные таблицы будут всегда составляться на диске, когда текст или таблицы блоба будут выбраны в запросе.

Это может создать основной хит производительности и даже привести к i/o узкому месту, если Ваш сервер получает какой-либо объем действия.

Проверьте, чтобы видеть, имеет ли какой-либо из Ваших столбцов этот тип данных. Если они, можно попытаться переписать запрос так, чтобы временная таблица не была составлена (группа всегда вызывает их, я думаю), или попытка, не выбирающая их. Другая стратегия состояла бы в том, чтобы разбить это в несколько меньших запросов, которые могли бы выполниться в части времени.

Удачи!

Попробуйте это:

SELECT i.id
FROM ((items AS i
   INNER JOIN items_keywords AS ik ON ( i.id = ik.item_id ))
   INNER JOIN keywords AS k ON ( k.id = ik.keyword_id ))
   INNER JOIN keywords_profiles AS kp ON ( k.id = kp.keyword_id AND kp.profile_id = 139)
ORDER BY i.timestamp DESC
LIMIT 20;

Я могу быть абсолютно неправым, но что происходит, когда Вы изменяетесь

WHERE kp.profile_id IN (139)

кому:

WHERE kp.profile_id = 139

Рассмотрение pastie.org связывается в комментариях с вопросом:

• Вы присоединяетесь items.source_id int(4) кому: sources.id int(16)
• также items.id int(16) кому: itemskeywords.item_id int(11)

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

Я понимаю, что это просто ширины дисплея и что фактический диапазон чисел, которые может сохранить столбец, определяется только частью INT, но в справочнике MySQL 6.0 говорится:

Обратите внимание, что при хранении больших значений, чем ширина дисплея в целочисленном столбце можно испытать проблемы, когда MySQL генерирует временные таблицы для некоторых сложных соединений, потому что в этих случаях MySQL предполагает что совпадения данных в ширину первоначального столбца.

От грубых фигур Вы заключили в кавычки, не выглядит, как будто Вы превышаете ширину дисплея на любом из столбцов ID. Можно также убрать эти несоответствия, хотя только устранить другую возможную ошибку.

Вы могли бы быть также для удаления ширин дисплея в целом, если у Вас нет потребности в них

править:

Я рисковал бы предположением, что исходный автор базы данных, возможно, думал это int(4) предназначенный "целое число максимум с 4 цифрами", тогда как это на самом деле означает "целое число между-2147483648 и 2 147 482 647 отображенных по крайней мере с 4 символами, лево-дополненными пробелами в случае необходимости"

Определения как authors.refreshed int(20) или items.timestamp int(30) действительно не имейте смысла, поскольку может только быть 10 цифр плюс знак в int. Даже a bigint не может превысить 20 символов. Возможно, исходный автор думал это int(4) походил varchar(4)?

Проблема появляется, что она имеет к полным соединениям через каждую таблицу, прежде чем она даже попытается сделать где пункт. Это может вызвать 500k строки на таблицу через, Вы смотрите в миллионах + строки, которые она заполняет в памяти. Я попытался бы изменить СОЕДИНЕНИЯ на ЛЕВЫЕ СОЕДИНЕНИЯ ИСПОЛЬЗУЯ ().

Попробуйте сделать резервную копию ваших таблиц. После этого переименуйте исходные таблицы во что-нибудь другое, переименуйте новые таблицы в исходные и попробуйте еще раз с таблицами с новыми, но старыми именами ...

Или вы можете попытаться восстановить таблицы, но этого не происходит. всегда помогать.

Edit: Человек, это был старый вопрос ...