CREATE VIEW для MySQL в течение прошлых 30 дней

Хорошо, время для психической отладки.

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

Это также позволит вам напрямую связать указанную таблицу с таблицей категорий без предварительного ее анализа.

Или, как сказал Крис Дейт: «Каждое пересечение строки и столбца содержит ровно одно значение из применимого домена (и ничего больше)».

SELECT `date`, title, category, url, comments 
                       FROM cute_news 
                      WHERE category LIKE '%45%' 
                   ORDER BY `date` DESC

The LIKE '%45%' означает, что необходимо выполнить полное сканирование таблицы. Возможно, вы храните список категорий в столбце? Если да, то создание новой таблицы, хранящей категорию и news_article_id, позволит использовать индекс для более эффективного извлечения совпадающих записей.

Добавление подстановочного знака в левой части сравнения значений:

LIKE '%xyz'

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

Нормализация данных была бы еще одним шагом, который следует рассмотреть - категории, вероятно, должны быть в отдельной таблице.

Я предполагаю, что ваш столбец category представляет собой список значений категорий, например «12,34,45,78»?

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

Некоторые люди предлагают использовать полнотекстовый поиск вместо предиката LIKE с подстановочными знаками, но в этом случае проще создать другую таблицу, чтобы вы могли перечислить одно значение категории в каждой строке со ссылкой на свой cute_news таблица:

CREATE TABLE cute_news_category (
  news_id INT NOT NULL,
  category INT NOT NULL,
  PRIMARY KEY (news_id, category),
  FOREIGN KEY (news_id) REFERENCES cute_news(news_id)
) ENGINE=InnoDB;

Затем вы можете запросить, и он будет работать намного быстрее:

SELECT n.`date`, n.title, c.category, n.url, n.comments 
FROM cute_news n
JOIN cute_news_category c ON (n.news_id = c.news_id)
WHERE c.category = 45 
ORDER BY n.`date` DESC

Любой ответ - это предположение, покажите:
- соответствующие результаты SHOW CREATE TABLE
- вывод EXPLAIN из ваших общих запросов.

И комментарий Билла Карвина, безусловно, применим.

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

Все, что содержит LIKE '%XXX%', будет медленным. Это медленная операция.

Для таких вещей, как категории, вы можете выделить категории в другую таблицу и использовать внешний ключ в таблице cute_news. Таким образом, вы сможете иметь category_id и использовать его в запросе, что будет намного быстрее.

Также я не совсем понимаю, почему вы говорите об использовании CREATE VIEW. Представления не помогут вам в скорости. Только если это не материализованное представление, которое MySQL не поддерживает.

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

Это особенно применимо, поскольку, судя по всему, ваши данные нужно обновлять только раз в 30 дней.