Форум MySQL

Всем привет. Прошу помощи подправить мои запросы. время исполнения первого 73мс, второго 93мс...

SELECT    p.`em_partner_file`,    p.`em_partner_title` FROM    `em_partner_block` p JOIN `em_category` c ON (p.`em_category_id`=c.`em_category_parent_id`) WHERE    p.em_category_id = (                       SELECT                  em_category_parent_id               FROM              `em_category`               WHERE               em_category_id = 40                       ) AND     p.`em_partner_access`='y' AND    p.`em_partner_deleted`='n' GROUP BY    p.`em_partner_id` ORDER BY    p.`em_partner_sort` ASC

SELECT    p.`em_partner_file`,    p.`em_partner_title` FROM    `em_partner_block` p JOIN `em_category` c ON (p.`em_category_id`=c.`em_category_parent_id`) WHERE    p.`em_category_id` = (                     SELECT             `em_category_parent_id`             FROM             `em_category`             WHERE             `em_category_id` = (                                         SELECT                         `em_category_parent_id`                     FROM                         `em_category`                     WHERE                         `em_category_id` = 303                                            )                        ) AND     p.`em_partner_access`='y' AND    p.`em_partner_deleted`='n' GROUP BY    p.`em_partner_id` ORDER BY    p.`em_partner_sort` ASC

Получается такая картина... На сайте тройная вложенность страниц...На первой мы пишем простой запрос для выборки данных из таблицы категории. На второй странице мы определяем подкатегорию после чего мы это значение вставляем в основной запрос. На третей странице у нас похожая ситуация. Мы определяем подподкатегорию, вставляем в запрос подкатегории, после чего вставляем в основной запрос. Как упростить кто подскажет? Спасибо.

1) А проблема в чем заключается, в том что запросы медленно выполняются?
2) Если не сложно прикрепите дампы таблиц (без данных), которые получаются при помощи запроса SHOW CREATE TABLE (на ключи хочется посмотреть). И сообщите объем таблиц (в Мб).

PS Вложенные запросы (в вашем случае точно) можно преобразовать в объединение таблиц, что позволит ускорить выборку.

Для начала первый запрос следует преобразовать как-то так

SELECT     p.`em_partner_file`,     p.`em_partner_title`  FROM     `em_partner_block` p  JOIN `em_category` c  ON (p.`em_category_id`=c.`em_category_parent_id`) JOIN `em_category` cp ON (p.em_category_id = cp.em_category_parent_id AND cp.em_category_id = 40) WHERE    p.`em_partner_access`='y' AND     p.`em_partner_deleted`='n'  GROUP BY     p.`em_partner_id`  ORDER BY     p.`em_partner_sort` ASC

этот запрос 94мс выполняется, мой что первый 73мс

Добавьте индекс на столбец em_category_id таблицы em_partner_block. Как изменилось время?

PRIMARY KEY  (`em_partner_id`)   KEY `em_category_id` (`em_category_id`),

так? это в таблице em_partner_block

Да, только запятая после PRIMARY KEY, а не после KEY.

В уже в существующую таблицу можно добавить его так

ALTER TABLE `em_partner_block` ADD INDEX ( `em_category_id` )

да никак, 94мс так и осталось

А теперь попробуйте вот такой запрос - у него время уменьшается?

SELECT     p.`em_partner_file`,     p.`em_partner_title`  FROM     (SELECT * FROM `em_partner_block`     WHERE em_partner_access='y' AND             em_partner_deleted='n') AS p  JOIN `em_category` c  ON (p.`em_category_id`=c.`em_category_parent_id`)  WHERE     p.em_category_id = (                        SELECT                   em_category_parent_id                FROM               `em_category`                WHERE                em_category_id = 40                        ) GROUP BY     p.`em_partner_id`  ORDER BY     p.`em_partner_sort` ASC

PS Вообще бы, конечно, полный бы дамп - с ним повозиться, так как EXPLAIN у меня жалуется на отсутствие данных, не понятно какой индекс используется, а какой нет.

Время аналогичное. А нельзя ли сделать полостью на джоинах? А то вложенные запросы как говорилось замедляют исполнение. Насчет дампа - с данными что ли?

Можно (смотрите ниже), но дело не в JOIN-ах и вложенных запросах, а в том, чтобы подцепить индексы, а они судя по всему почему-то не подцепляются. Поставьте перед SELECT-запросом ключевое слово EXPLAIN - посмотрите, что он сообщает.

SELECT      p.`em_partner_file`,      p.`em_partner_title`   FROM       (SELECT * FROM `em_partner_block`      WHERE em_partner_access='y' AND              em_partner_deleted='n') AS p   JOIN `em_category` c   ON (p.`em_category_id`=c.`em_category_parent_id`)  JOIN `em_category` cp  ON (p.em_category_id = cp.em_category_parent_id AND cp.em_category_id = 40)  GROUP BY      p.`em_partner_id`   ORDER BY      p.`em_partner_sort` ASC

EXPLAIN поставил в самом начале запроса перед селект...см. приложение

Скорость не изменилась? По прежнему 94мс? Кстати, имеются в виду микро или милисекунды (может уже ограничение в подсчете времени и точно у этих значений скорость уже не измерить)?

PS Ключи используется, по сравнению с первым запросом мы перевели полностью его на объединения, даже избавились от WHERE-конструкции. Индексы тоже вроде все в наличии.

уже реже чем 94мс стало выполнятся...колеблется от 63мс до 94мс

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

ок, спасибо, буду тестировать

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

SELECT     p.`em_partner_file`,     p.`em_partner_title`  FROM     `em_partner_block` p  JOIN `em_category` c  ON (p.`em_category_id`=c.`em_category_parent_id`)  WHERE     p.`em_category_id` = (                      SELECT              `ic.em_category_parent_id`              FROM              `em_category` ic             JOIN `em_category` ip             ON (ic.em_category_id = ip.em_category_parent_id AND                 ip.em_category_id = 300)) AND      p.`em_partner_access`='y' AND     p.`em_partner_deleted`='n'  GROUP BY     p.`em_partner_id`  ORDER BY     p.`em_partner_sort` ASC

этот вообще ругнулся Unknown column 'ic.em_category_parent_id' in 'field list', не пойму где ошибка

Сейчас попробую в минимально формате базу данных воспроизвести и повозиться с запросами непосредственно, а не умозрительно.

Исправьте запрос следующим образом

SELECT      p.`em_partner_file`,      p.`em_partner_title`   FROM      `em_partner_block` p   JOIN `em_category` c   ON (p.`em_category_id`=c.`em_category_parent_id`)   WHERE      p.`em_category_id` = (                       SELECT               ic.em_category_parent_id             FROM               `em_category` ic              JOIN `em_category` ip              ON (ic.em_category_id = ip.em_category_parent_id AND                  ip.em_category_id = 300)) AND       p.`em_partner_access`='y' AND      p.`em_partner_deleted`='n'   GROUP BY      p.`em_partner_id`   ORDER BY      p.`em_partner_sort` ASC

1 - вложенные запросы дольше выполняются как пишет народ...поэтому хочется оптимизации
2

CREATE TABLE `em_category` (   `em_category_id` int(10) unsigned NOT NULL auto_increment,   `em_category_parent` int(10) unsigned NOT NULL default '0',   `em_category_module` enum('produce','service') collate utf8_unicode_ci NOT NULL default 'produce',   `em_category_idn` varchar(20) character set cp1251 NOT NULL default '',   `em_element_id` tinyint(3) unsigned NOT NULL default '0',   `em_language_id` tinyint(3) unsigned NOT NULL default '1',   `em_category_parent_id` int(10) unsigned NOT NULL default '0',   `em_category_level` tinyint(3) unsigned NOT NULL default '1',   `em_category_name` varchar(64) collate utf8_unicode_ci NOT NULL default '',   `em_category_image` varchar(255) character set cp1251 NOT NULL default '',   `em_category_weight` smallint(5) unsigned NOT NULL default '100',   `em_category_count_produce` int(10) unsigned NOT NULL default '0',   `em_category_count_service` int(10) unsigned NOT NULL default '0',   `em_category_count_company` int(10) unsigned NOT NULL default '0',   `em_category_count_event` int(10) unsigned NOT NULL default '0',   `em_category_access` enum('y','n') collate utf8_unicode_ci NOT NULL default 'y',   `em_category_deleted` enum('y','n') collate utf8_unicode_ci NOT NULL default 'n',   PRIMARY KEY  (`em_category_id`),   KEY `em_category_parent_id` (`em_category_parent_id`),   KEY `em_category_name` (`em_category_name`),   KEY `em_category_weight` (`em_category_weight`),   KEY `em_category_access` (`em_category_access`,`em_category_deleted`),   KEY `em_element_id` (`em_element_id`),   KEY `em_category_parent` (`em_category_parent`),   KEY `em_category_level` (`em_category_level`),   KEY `em_language_id` (`em_language_id`) ) ENGINE=MyISAM AUTO_INCREMENT=6128 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `em_partner_block` (   `em_partner_id` int(3) unsigned NOT NULL auto_increment,   `em_category_id` int(5) unsigned NOT NULL default '0',   `em_partner_title` varchar(50) collate utf8_unicode_ci NOT NULL default '',   `em_partner_file` varchar(50) collate utf8_unicode_ci NOT NULL default '',   `em_partner_sort` int(3) unsigned NOT NULL default '0',   `em_partner_access` enum('y','n') collate utf8_unicode_ci NOT NULL default 'y',   `em_partner_deleted` enum('y','n') collate utf8_unicode_ci NOT NULL default 'n',   PRIMARY KEY  (`em_partner_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

по объему не скажу, нужно писать скрипт, что не очень хочется...из дампа видно количество записей

>по объему не скажу, нужно писать скрипт, что не очень хочется...из дампа видно количество
>записей
Посмотрите в SHOW STATUS или прямо в phpMyAdmin - это мета-информация, она в заголовках таблиц хранится.

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

Выполните запросы

SHOW TABLE STATUS LIKE 'em_category' \G SHOW TABLE STATUS LIKE 'em_partner_block' \G

Какие значения в поле Data_length?

em_category - 349088
em_partner_block - 180

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

на сервере удаленно

Кстати, а что за столбец p.`em_partner_id` в GROUP BY? Он какую функцию несет?

группирует, без него выводит дубли...странно в общем-то