Как оптимизировать этот медленный запрос?

У меня есть следующий запрос, который выполняется очень медленно (почти 50000 записей)

SELECT
  news.id,
  news.title,
  DATE_FORMAT(news.date_online,'%d %m %Y')AS newsNL_Date
  news_categories.parent_id
FROM 
  news,
  news_categories
WHERE 
  DATE(news.date_online)=2013-02-25 
  AND news.category_id = news_categories.id
  AND news.date_online < NOW()
  AND news.activated ='t' 
ORDER BY 
  news.date_online DESC

У меня клиент MySQL версии 5.0.96

Когда я запускаю вызов EXPLAIN EXTENDED с этим запросом, я получаю следующий результат:

id    select_type   table             type     possible_keys                        key         key_len     ref               rows     Extra
1     SIMPLE        news              ref      category_id,date_online,activated    activated   1           const             43072    Using where; Using filesort
1     SIMPLE        news_categories   eq_ref   PRIMARY,id                           PRIMARY     4           news_category_id  1

У меня есть индекс по следующим столбцам news_id (первичный ключ) date_online активирован category_id

Когда я смотрю на результат EXPLAIN EXTENDED, я вижу USING_WHERE; ИСПОЛЬЗОВАНИЕ FILESORT. Я знаю, что они оба плохие, но не знаю, как это исправить.


mysql performance explain
person TutanRamon    schedule 13.03.2013    source источник

Ответы (4)


arrow_upward
-1
arrow_downward

Ваш запрос получает данные за определенный день, поэтому в этом случае вы можете опустить часть AND news.date_online < NOW() (возможно, оптимизатор запросов сделает это за вас).

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

Затем вы должны создать многостолбцовый индекс для столбцов, которые вы используете, потому что я предполагаю, что category_id, date_online и activated используются почти в каждом запросе. Это сделает вставки немного медленнее, но, судя по тому, как это выглядит (новостное приложение), будет гораздо больше операций чтения, чем вставок.

person nico gawenda    schedule 13.03.2013

arrow_upward
1
arrow_downward

Попробуйте добавить индексы для date_online (тип B-tree). Также я бы попытался избежать использования NOW () в close, а лучше установить его в переменной и вместо этого использовать переменную.

Я полагаю, что поля id являются ключами, поэтому они уже проиндексированы.

person idipous    schedule 13.03.2013
comment
Индекс в date_online игнорируется, поскольку вы не используете этот столбец напрямую, а выполняете функцию для значения столбца и сравниваете результат - person Kickstart; 13.03.2013

arrow_upward
1
arrow_downward

Используйте LEFT JOIN и увидите разницу

SELECT
  news.id,
  news.title,
  DATE_FORMAT(news.date_online,'%d %m %Y')AS newsNL_Date
  news_categories.parent_id
FROM 
  news
LEFT JOIN news_categories ON news_categories.id = news.category_id
WHERE 
  DATE(news.date_online)= '2013-02-25'
  AND DATE(news.date_online) < DATE(NOW())
  AND news.activated ='t' 
ORDER BY 
  news.date_online DESC
person Muhammad Raheel    schedule 13.03.2013

arrow_upward
1
arrow_downward

В дополнение к другим полезным предложениям у вас есть DATE (news.date_online) = 2013-02-25. Казалось бы, это вынуждает MySQL преобразовывать news.date_online в другой формат для каждой строки в таблице. Это остановит использование индексов.

Возможно, измените его на news.date_online BETWEEN '2013-02-25 00:00:00' AND '2013-02-25 23:59:59', что должно (я думаю) разрешить индексирование date_online будет использоваться

person Kickstart    schedule 13.03.2013