Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Прошу помощи в оптимизации запроса / 8 сообщений из 8, страница 1 из 1
07.07.2017, 14:40
    #39484585
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Приветствую! В новостном сайте есть один противный запрос, который постоянно валится в лог медленных запросов:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT
  t1.id, 
  t1.date_add,
  t1.file1,
  t1.title_ru AS title,
  t1.text_small_ru AS text_small,
  t1.text_big_ru AS text_big,
  t2.name_ru AS category_name
FROM articles AS t1, categories AS t2
WHERE
  t2.id=t1.id_section AND
  t2.id_parent=2 AND
  t1.enable=1 AND
  t1.status<>1 AND
  t1.title_ru <> '' AND
  t1.date_add<>0 AND
  t1.date_add <= NOW() AND
  (t2.id=3 OR t2.id=4 OR t2.id=7 OR t2.id=1034 OR t2.id=2018 OR t2.id=2020)
ORDER BY t1.date_add DESC
LIMIT 0, 30



 
таблицы
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_section` int(11) unsigned DEFAULT NULL,
  `date_add` datetime DEFAULT NULL,
  `file1` varchar(255) DEFAULT '',
  `file1caption` varchar(255) NOT NULL,
  `title_ru` varchar(255) DEFAULT NULL,
  `title_manual_ru` varchar(255) DEFAULT NULL,
  `text_small_ru` text,
  `text_big_ru` mediumtext,
  `keywords_ru` varchar(255) DEFAULT NULL,
  `description_ru` varchar(255) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `enable` int(1) unsigned DEFAULT NULL,
  `top_text` varchar(255) NOT NULL,
  `top_place` tinyint(4) NOT NULL,
  `status` int(10) unsigned DEFAULT '0',
  `status2` tinyint(4) NOT NULL,
  `allow_rating` tinyint(4) NOT NULL DEFAULT '0',
  `rating_value` float(5,4) NOT NULL DEFAULT '0.0000',
  `rating_count` int(10) unsigned NOT NULL DEFAULT '0',
  `viewed` int(11) unsigned DEFAULT '0',
  `order_id` int(11) NOT NULL DEFAULT '0',
  `count_comments` smallint(5) unsigned NOT NULL DEFAULT '0',
  `search_index` longtext NOT NULL,
  `search_index_title` text NOT NULL,
  `ph_small` varchar(100) NOT NULL,
  `ph_title` varchar(250) NOT NULL DEFAULT '',
  `ph_author` varchar(100) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `mode` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` int(10) unsigned NOT NULL DEFAULT '0',
  `source` tinyint(4) NOT NULL DEFAULT '0',
  `allow_comments` int(11) NOT NULL DEFAULT '1',
  `regionid` int(11) NOT NULL,
  `use_on_rss1` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `section` (`id_section`),
  KEY `date_add` (`date_add`),
  KEY `regionid` (`regionid`),
  FULLTEXT KEY `search_title` (`title_ru`,`search_index_title`),
  FULLTEXT KEY `search` (`title_ru`,`text_big_ru`,`search_index`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;


DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_parent` int(11) DEFAULT '0',
  `file1` varchar(255) DEFAULT '',
  `title_ru` varchar(255) NOT NULL,
  `name_ru` varchar(255) DEFAULT NULL,
  `text_small_ru` text,
  `text_ru` mediumtext,
  `description_ru` text,
  `keywords_ru` text,
  `status` tinyint(4) DEFAULT '1',
  `order_id` int(11) NOT NULL DEFAULT '0',
  `order_id_main` int(11) NOT NULL DEFAULT '0',
  `allow_news` tinyint(4) DEFAULT '1',
  `search_index` longtext NOT NULL,
  `search_index_title` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_parent` (`id_parent`),
  FULLTEXT KEY `search_title` (`title_ru`,`search_index_title`),
  FULLTEXT KEY `search` (`title_ru`,`text_ru`,`search_index`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;


Запрос вроде бы простой, но работает плохо...

размер базы articles ~500 mb, categories - 1.2 mb.

На локальной машине запрос не выполняется вообще: висит пару минут со статусом "Copying to tmp table", после чего останавливается по таймауту. На хостинге бывает по разному, от 2-3 до 7 секунд, но не всегда. В основном отрабатывает шустро. Но проблема, как я понимаю, тем не менее присутствует. Подскажите, как можно оптимизировать данный запрос?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2017, 14:49
    #39484599
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Какая селективность по каждому из полей в условиях связывания и отбора?
Почему для последнего условия не использована форма
Код: sql
1.
AND t2.id IN (3,4,7,1034,2018,2020)

?
Где собственно EXPLAIN запроса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2017, 15:09
    #39484626
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
AkinaКакая селективность по каждому из полей в условиях связывания и отбора?
что вы имеете в виду?

AkinaПочему для последнего условия не использована форма
пробовал и через IN, и через JOIN. Везде показывает одинаковый план запроса.

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2017, 15:11
    #39484629
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Забыл написать, в articles ~150 тыс. строк, в categorys 100
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2017, 15:21
    #39484642
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Victor256 , для таблицы categories явно напрашивается индекс по (id,id_parent).
Для таблицы articles - как минимум по (id_section, enable)... хотя не исключаю, что не хуже будет и по (id_section, date_add), а также один из них с добавлением других полей.

Victor256что вы имеете в виду?
Да собственно именно селективность. Грубо - какой в среднем процент записей отбирается из всего массива при отборе по определённому значению поля.

Victor256пробовал и через IN, и через JOIN. Везде показывает одинаковый план запроса
Было бы удивительно, коли не так - запрос-то один и тот же, просто формы его записи разные.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.07.2017, 15:23
    #39484646
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Опять же - по второй таблице можно и покрывающий индекс создать. Большой только получится, зараза...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.07.2017, 00:42
    #39484976
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
Victor256,

1, если категории меняют id_parent очень редко или не менют вообше, то можно
вынести (денормализировать) id_parent в таблицу articles. и избавится от жоинта вообше.

2. насколько надо показывать старые артикли? какая часть
новостей может быть активна?. Это к тому, что если допустимо по
бизнес логике , добавьте, например
...АНД ( t1.date_add > NOW() - 30 дней)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.07.2017, 13:37
    #39485742
Victor256
Victor256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи в оптимизации запроса
В общем экспериментировал с разными индексами, сильно смущало наличие "using temporary" в плане, т.к. именно на этом этапе запрос долго подвисал в списке процессов.


потом в какой-то момент внезапно начал использоваться индекс по полю date_add. И несмотря на цифры в плане, выполняется на 2-3 порядка быстрее:


На всякий случай прописал USE INDEX. Буду наблюдать.

javajdbc1, если категории меняют id_parent очень редко или не менют вообше, то можно
вынести (денормализировать) id_parent в таблицу articles. и избавится от жоинта вообше.
категории меняются, к сожалению постоянно. Вынести нельзя. По уму, вообще надо было сделать отдельно таблицу articles с полями, занимающими много места и отдельно всё остальное из той же таблицы. И либо джоинить таблицу с текстами последней либо отдельным запросом выбирать уже по готовым айди.

javajdbc2. насколько надо показывать старые артикли? какая часть
новостей может быть активна?. Это к тому, что если допустимо по
бизнес логике , добавьте, например
...АНД ( t1.date_add > NOW() - 30 дней)
Допустимо. Очень хорошая идея. Попробовал, получилось вместо полной таблицы теперь просматриваются порядка 5тыс. строк. Можно уменьшить кол-во дней и ещё больше сократить диапазон. Ну и запросы теперь, учитывая кеширование выполняются за 0.001-0.005 сек.

Всем большое спасибо за помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Прошу помощи в оптимизации запроса / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]