Как оптимизировать запрос к бд?

Question

[Виктор Таран]

->addFrom("LEFT JOIN `betaintranet`.`ad_c`","`tgb`","`node`.`nid` = `tgb`.`obj_id` AND `tgb`.`start_date`<= NOW() AND `tgb`.`end_date` >= NOW() AND `tgb`.`ban_type` < '4' /* vrezka */")
			->addField("COUNT(DISTINCT `tgb`.`bid`)","`tgbactiv`")			
			->addField("`tgb`.`bid`","`tgbid`")
			->addField("`tgb`.`phone`","`tgbphonebaze`")
		
		->addFrom("LEFT JOIN `betaintranet`.`ad_banners`","`tgbinfo`","`tgb`.`bid` = `tgbinfo`.`bid`")
			->addField("`tgbinfo`.`filename`","`tgbimageurl`")
			->addField("`tgbinfo`.`url`","`tgburl`")
			->addField("`tgbinfo`.`text`","`tgbtext`")
			->addField("`tgbinfo`.`footer`","`tgbprice`")
			->addField("`tgbinfo`.`header_text`","`tgbtitle`")

Полный запрос монструозен до неприличия, но этот дждйн тормозит страничку до минуты.
А по факту берет 1 картинк 1 айди 1 тайтл 1 дескрипшен ;(
я так понимаю что очень медленно работает < 4.
Указав явное значение 2 скорость занчительно возрастает.
Вопрос как лучше сделать этот запрос с точк изрения производительнсоти.

Comments

[Евгений Самсонов]

Смотреть EXPLAIN.
И нужен полный запрос + схемы таблиц

[Виктор Таран]

Боюсь что запрос не для слабонервных не уверен что это пойдет в рамках бесплатной помощи на тостере, дело в том что "кодер" который это дела не парился с оптимизацией и выбирал все данные для страницы в 1 массив.
даже не спрашивайте..
учитывая что еще все это запрашивается при старте шаблона страницы в не зависимости от надобности этих данных на странице вообще. И как минимум 90% этих данных есть в бд текущего движка, запросы идут с 3 бд.
В общем я могу конечно дать весь запрос,
Но по нему сразу станет понятно что это не самая лучшая идея для обсуждения.
я нашел самые медленные места.
Вот эти джойны замедляют страничку раз 10 а по факту там две таблицы по 500 значений и 400. соответственно.

ad_c

CREATE TABLE `ad_c` (
`cid` int(10) NOT NULL,
`ad_name` varchar(450) NOT NULL,
`bid` int(10) NOT NULL,
`ban_type` int(3) NOT NULL COMMENT '1-3 ТГБ, 4 - крстр, 5 - 940x90 середина, 6 - брендирование, 7 - 940x90 верхний, 8 - 940x90 - нижний',
`obj_id` int(10) NOT NULL,
`ra_id` int(10) NOT NULL,
`dev_id` int(10) NOT NULL,
`w_site` tinyint(1) NOT NULL COMMENT '1 - Nk, 2 - SPB, 3 - NP',
`m_invoice` int(11) NOT NULL DEFAULT '0',
`start_date` datetime NOT NULL,
`end_date` datetime NOT NULL,
`stopflag` tinyint(1) NOT NULL DEFAULT '0',
`zaglusha` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'является ли заглушкой',
`phone` varchar(20) DEFAULT NULL COMMENT 'номер телефона для базы',
`dyn_number` tinyint(1) NOT NULL,
`whocreated` int(11) NOT NULL COMMENT 'ID создателя',
`time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Когда создал',
`whoedited` int(11) DEFAULT NULL COMMENT 'ID редактора',
`time_edited` timestamp NULL DEFAULT NULL COMMENT 'Когда редактировал'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `ad_c`
--

INSERT INTO `ad_c` (`cid`, `ad_name`, `bid`, `ban_type`, `obj_id`, `ra_id`, `dev_id`, `w_site`, `m_invoice`, `start_date`, `end_date`, `stopflag`, `zaglusha`, `phone`, `dyn_number`, `whocreated`, `time_created`, `whoedited`, `time_edited`) VALUES
(13, 'РА Нектарин // MR GROUP// ЖК Пресня Сити', 96, 1, 11866, 35, 2469, 1, 0, '2019-06-28 00:01:01', '2020-01-31 23:59:59', 0, 0, '', 1, 24, '2019-06-28 12:07:42', 16, '2019-12-30 12:48:53'),
(14, 'Риэлти Проджект/ ЛСР / Ленинградка 58', 97, 1, 14416, 43, 558, 1, 0, '2019-06-28 00:01:01', '2019-07-31 23:59:59', 0, 0, '', 1, 24, '2019-06-28 12:12:19', 16, '2019-07-25 14:58:03'),
(15, 'РА Нектарин / // ЖК Ты и Я / Мангазея', 98, 1, 13868, 0, 8573, 1, 0, '2019-06-28 00:01:01', '2019-06-30 23:59:59', 0, 0, NULL, 1, 24, '2019-06-28 12:15:11', NULL, NULL),

ad_banners

- Структура таблицы `ad_banners`
--

CREATE TABLE `ad_banners` (
`bid` int(10) NOT NULL,
`header_text` varchar(255) NOT NULL,
`text` varchar(255) NOT NULL,
`footer` varchar(255) NOT NULL,
`url` varchar(400) NOT NULL,
`filename` varchar(255) NOT NULL,
`onlyfilename` varchar(255) DEFAULT NULL,
`onlyext` varchar(255) DEFAULT NULL,
`zeropixel` varchar(350) NOT NULL,
`slider` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `ad_banners`
--

INSERT INTO `ad_banners` (`bid`, `header_text`, `text`, `footer`, `url`, `filename`, `onlyfilename`, `onlyext`, `zeropixel`, `slider`) VALUES
(96, 'Жилой Комплекс «Пресня Сити»', 'Дом сдан! 7 мин. пешком от метро Улица 1905 года. В 4км от Кремля! Детский сад, центр раннего развития, подземный многоуровневый паркинг.', '[phone]', 'https://wcm-ru.frontend.weborama.fr/fcgi-bin/dispa...', '321676presnyaka_opop_15mln_dudi.jpg', '321676presnyaka_opop_15mln_dudi', 'jpg', '', 0),
(97, 'ЖК Ленинградка 58, м. Водный стадион', 'Квартиры бизнес-класса от 6,9 млн ₽. Панорамные виды. Выгода до 600 000 р. Рассрочка на 2 года. Звоните!', '[phone]', 'leningradka58.ru/?utm_source=naydikvartiru&utm_con...', '339773tptptpopop.jpg', '339773tptptpopop', NULL, '', 0),

НА выходе этого джойна мы получаем
Тайтл банера ( видов банеров действиельно 4)
текст банера
картинку
и статус что он работает.

Весь же запрос тупо не влезет в лимит размера на тостере ;(( но могу дать ссылкой
Весь запрос https://naydikvartiru.ru/sql.txt

[Евгений Самсонов]

Виктор Таран, EXPLAIN что показывает?

[ThunderCat]

Мне кажется, или индексов там нет вообще?

[Виктор Таран]

ThunderCat, тут вообще полная самопись, разраба который это чудо делал естественно нет. О каких -то индексах в этом проекте я даже не думал. В виду 100% их отсутствия.

[ThunderCat]

Виктор Таран, Как вообще можно задумываться о скорости исполнения запросов, если индексов на таблицах нет? Без них все будет работать полным перебором вариантов, конечно это полная опа. Ставьте индексы, я вижу там даже первичного ключа нет, вообще ахтунг...

[Виктор Таран]

ThunderCat,
ну тут я немного переборщил, все-же оказалось что там что-то есть.
Главное что меня смущяет чт сам запрос ничего особого не представляет.
и делается реально быстро.
Я так понимаю что проблема в

LEFT JOIN `nk_xml_import`.`xml_places`  `xml`  ON `node`.`nid` = `xml`.`build_place` /* metka */
LEFT JOIN `nk_xml_import`.`kvartiri`  `xmlrooms`  ON `xml`.`yandex_building_id` = `xmlrooms`.`yaid` /* metka rooms */

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

[ThunderCat]

Виктор Таран, на ВСЕ поля, участвующие в запросе в условии джоин, вэа, ин, груп бай и ордер должны быть индексы, иначе будет тупым перебором выбираться, а это пипец как медленно. Ну и id должн быть первичным ключом естественно.

Answer 1

[Антон Шаманов]

NOW() NOT BETWEEN `tgb`.`start_date` AND `tgb`.`end_date`

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

`tgb`.`start_date`<= NOW() AND `tgb`.`end_date` >= NOW() AND `tgb`.`ban_type` < '4'

в WHERE.