Форум пользователей MySQL

jerry · 31.01.2011 21:00:25

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

есть две таблицы

CREATE TABLE `product_description` (

  `product_id` int(11) NOT NULL auto_increment,

  `language_id` int(11) NOT NULL,

  `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,

  `description` text character set utf8 collate utf8_unicode_ci,

  PRIMARY KEY  (`language_id`,`product_id`),

  KEY `name_2` (`name`),

  FULLTEXT KEY `name` (`name`),

  FULLTEXT KEY `description` (`description`)

) ENGINE=MyISAM AUTO_INCREMENT=359725 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=359725 ;

CREATE TABLE `product_to_category` (

  `product_id` int(11) NOT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY  (`category_id`,`product_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

(1) есть запрос

SELECT * 

FROM product_description pd

LEFT JOIN product_to_category p2c ON ( pd.product_id = p2c.product_id ) 

WHERE pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY pd.product_id DESC 

LIMIT 0 , 24

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2c ref PRIMARY PRIMARY 4 const 2251 Using where; Using index; Using temporary; Using f...
1 SIMPLE pd eq_ref PRIMARY,language_id,product_id PRIMARY 8 const,catalog.p2c.product_id 1
время выполнения 0,5 сек.

(2) и есть запрос

SELECT * 

FROM product_description pd

LEFT JOIN product_to_category p2c ON ( pd.product_id = p2c.product_id ) 

WHERE pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY p2c.product_id DESC 

LIMIT 0 , 24

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2c ref PRIMARY PRIMARY 4 const 2251 Using where; Using index
1 SIMPLE pd eq_ref PRIMARY,language_id,product_id PRIMARY 8 const,catalog.p2c.product_id 1
время выполнения 0,0020 сек.

Как видите, вся разница в условии сортировки: в первом это pd.product_id, во втором p2c.product_id.
Правильно ли я понимаю, что такое сильное торможение первого связано с тем, что сортировка проводится по таблице, которая была присоединена не в самом начале? То есть всякую сортировку(в большинстве случаев) стоит проводить по полям той таблицы, которая присоединяется первой (идёт в EXPLAIN-е первой), в данном случае это product_to_category.
Если я прав, то есть ли вобще какие нибудь пути сделать первый запрос быстрым? -понятно, что product_id и там и там один, но в таблице product_description есть, например, поле name по которому также необходимо проводить сортировки.
//изменять порядок присоединения таблиц с помощью STRAIGHT_JOIN уже пробовал - в итоге оба получаются тормознутыми.
//также изменял ключ у product_to_category с PRIMARY KEY (`category_id`,`product_id`) на PRIMARY KEY (`product_id`, `category_id`) - первый запрос выполняется 0,07 сек и исходя из EXPLAIN таблица product_description присоединяется первой. Здесь это более-менее исправляет ситуацию, но есть запросы в которых изменением/добавлением индексов не получается заставить необходимую таблицу(по полям которой будет происходить сортировка) присоединяться первой -там просто идёт несколько JOIN-ов с WHERE по каждой таблице и оптимизатору судя по всему выгоднее ставить в начало другие таблицы.

paulus · 31.01.2011 21:47:48

Почему, ну почему все пишут левое объединение там, где место внутреннему
объединению?

Попробуйте:
1. Использовать внутреннее объединение (и всегда-всегда использовать их
кроме случаев, когда внешнее правда нужно);
2. Решить, какая сортировка Вам таки нужна. Если нужна сортировка по имени,
то нужен индекс на (language_id, name).

P.S. Кстати, помимо использования объединения, Вы еще не попробовали FORCE
INDEX, он бы прошел нормально в случае сортировки: по name то у Вас ключ есть.

jerry · 31.01.2011 22:22:26

пробовал
вот

SELECT * 

FROM product_description pd

FORCE INDEX ( language_id ) 

INNER JOIN product_to_category p2c ON ( pd.product_id = p2c.product_id ) 

WHERE pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY pd.name DESC 

LIMIT 0 , 24

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pd ref language_id language_id 4 const 55580 Using where
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 const,catalog.pd.product_id 1 Using index

индекс language_id на (language_id, name)
прироста к скорости нет (0,5 сек.)

подскажите, пожалуйста, вот здесь я в какой-то степени прав?

Правильно ли я понимаю, что такое сильное торможение первого связано с тем, что сортировка проводится по таблице, которая была присоединена не в самом начале? То есть всякую сортировку(в большинстве случаев) стоит проводить по полям той таблицы, которая присоединяется первой (идёт в EXPLAIN-е первой)

jerry · 01.02.2011 15:00:04

Давайте я опишу всё более подробно с реальным запросом

в запросе участвуют 3 таблицы

CREATE TABLE `product` (

  `product_id` int(11) NOT NULL auto_increment,

  `model` varchar(64) collate utf8_bin NOT NULL,

  `sku` varchar(64) collate utf8_bin NOT NULL,

  `status` int(1) NOT NULL default '0',

  `date_added` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`product_id`)

) ENGINE=MyISAM AUTO_INCREMENT=359725 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=359725 ;

CREATE TABLE `product_description` (

  `product_id` int(11) NOT NULL auto_increment,

  `language_id` int(11) NOT NULL,

  `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,

  `description` text character set utf8 collate utf8_unicode_ci,

  PRIMARY KEY  (`language_id`,`product_id`),

  KEY `language_id` (`language_id`,`name`),

  KEY `name_2` (`name`),

  FULLTEXT KEY `name` (`name`),

  FULLTEXT KEY `description` (`description`)

) ENGINE=MyISAM AUTO_INCREMENT=359725 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=359725 ;

CREATE TABLE `product_to_category` (

  `product_id` int(11) NOT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY  (`category_id`,`product_id`),

  KEY `product_id` (`product_id`,`category_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

В таблице product хранится общая информация о продуктах - 150к записей
В таблице product_description содержатся описания продуктов на 4-х языках. По 10к записей с языками language_id=2,3,4 и 150к записей с language_id=1. То есть на какие то продукты есть записи только на одном языке (language_id=1), на какие-то есть записи и на других языках. И того примерно 180к записей
В таблице product_to_category хранится информация о связях продукта с категориями. Каждый продукт относится в среднем сразу к двум категориям. Всего категорий 50, записей в таблице примерно 250к.

есть запрос, который отбирает продукты, отнесённые к заданной категории

SELECT * 

FROM product p

INNER JOIN product_description pd ON ( p.product_id = pd.product_id ) 

INNER JOIN product_to_category p2c  ON ( p.product_id = p2c.product_id ) 

WHERE p.status = '1'

AND p.date_available <= NOW( ) 

AND pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY pd.name DESC 

LIMIT 0 , 24

id     select_type     table     type     possible_keys                    key           key_len     ref                                   rows     Extra 

   SIMPLE           p2c    ref    PRIMARY,product_id           PRIMARY    4    const                               2487    Using index; Using temporary; Using filesort

   SIMPLE          pd    eq_ref    PRIMARY,language_id,product_id    PRIMARY    8    const,catalog.p2c.product_id    1    

   SIMPLE         p    eq_ref    PRIMARY                          PRIMARY    4    catalog.p2c.product_id        1    Using where

запрос занимает 0,5 сек. если убрать ORDER BY то он занимает 0,0020 сек; если поставить ORDER BY pd.product_id то запрос займёт 0,5сек; если поставить ORDER BY p2c.product_id то запрос займёт 0,0020сек

Подскажите пожалуйста, есть ли возможность при проведении сортировки по названию продукта добиться заметно лучшей скорости? Интересны любые методы. Просто полсекунды это очень много, так как данных предполагается приблизительно раз в 5-10 больше.

Отредактированно jerry (01.02.2011 21:32:42)

paulus · 01.02.2011 22:56:29

Фраза, обведенная в рамочку, верна отчасти. А отчасти — нет. Нет — потому что
MySQL выбирает порядок таблиц таким образом, чтобы уменьшить время выполнения
запроса. В частности, это относится и к сортировке. Кстати, LEFT JOIN мешают
менять таблицы местами, это тоже надо учитывать.

Что касается запроса — попробуйте рассказать оптимизатору приоритет индексов:
product_description pd FORCE INDEX (language_id): у Вас слишком много индексов
над близкими полями (например, PK начинается на тот же language_id), и оптимиза-
тор может неправильно выбирать индекс для работы с табличкой.

jerry · 01.02.2011 23:13:39

сделал

SELECT * 

FROM product p

INNER JOIN product_description pd

FORCE INDEX ( language_id ) ON ( p.product_id = pd.product_id ) 

INNER JOIN product_to_category p2c ON ( p.product_id = p2c.product_id ) 

WHERE p.status = '1'

AND p.date_available <= NOW( ) 

AND pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY pd.name DESC 

LIMIT 0 , 24

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra 

1    SIMPLE    pd    ref    language_id    language_id    4    const    55580    Using where

1    SIMPLE    p2c    eq_ref    PRIMARY,product_id    PRIMARY    8    const,catalog.pd.product_id    1    Using index

1    SIMPLE    p    eq_ref    PRIMARY    PRIMARY    4    catalog.p2c.product_id    1    Using where

разницы во времени никакой

Вот если например добавить какое нибудь поле (например name или просто заданный порядок сортировки) в таблицу product_to_category, которая выбирается оптимизатором первой при соединении таблиц, и делать ORDER BY уже по нему, то скорость оказывается сравнимой с 0,01 сек. Вобще может ли быть такая ситуация, когда стоит дублировать информацию (в данном случае поле name или какое либо другое) вот в такие "левые" таблицы (product_to_category) для ускорения работы в плане сортировок? -такие приёмы вобще используют (допустимо ли использовать в моём случае) или же всё-таки стоит искать другие более правильные ходы?

Отредактированно jerry (01.02.2011 23:26:09)

paulus · 02.02.2011 00:11:05

У Вас уже выбралась pd первой. Сортировки в памяти нет. Я ожидаю, что вот этот запрос
будет работать чрезвычайно быстро.

Если только доп.условия у Вас не такие, что пропускают кучу строк. Если такие — то
MySQL оказывался таки прав, и тогда мы пойдем другим путем (только расскажите,
что это действительно так)

Что касается вспомогательных таблиц — да, разумеется, делают. Это называется денор-
мализацией.

jerry · 02.02.2011 00:19:12

Если только доп.условия у Вас не такие, что пропускают кучу строк. Если такие — то
MySQL оказывался таки прав, и тогда мы пойдем другим путем

получается так, они пропускают много строк.. по сути там единственное условие это pd.language_id = '1'. В таблице всего 4 таких уникальных id, поэтому особого отсева и не происходит в данном случае. Если например поставить pd.language_id = '2', то скорость несколько возрастёт, потому как с language_id=2 всего 10к записей..

paulus · 02.02.2011 01:11:32

Ну вот, то есть MySQL был прав, когда ставил таблички в другом порядке

Тогда есть такой хитрый... ээ... назовем его финт ушами — если нам нужно
всё равно сортировать в памяти — лучше сортировать меньше данных, будет
быстрее. Делайте тогда не SELECT *, а SELECT id ... — у Вас будут отсортиро-
ванные по старому алгоритму id в памяти. Сортировать строки из 4 байт куда
быстрее, чем из 500 (?). К полученному обрезанному LIMIT (!) подзапросу
уже можно будет присоединить реальные данные через JOIN (т.е. сортируете
только id, а данные достаете только те, которые нужно).

Наверное, много слов, проще запросом:
SELECT p.* FROM product p JOIN (SELECT id ... длинный подзапрос) s USING (id).

jerry · 02.02.2011 13:27:05

Честно говоря не уловил мысли. Попробовал сделать несколько запросов, но они вобще не исполняются (как минимуму больше минуты занимают). Не могли бы написать зарос, который подразумевали.

>К полученному обрезанному LIMIT (!) подзапросу
если тут вы подразумевали просто сделать выборку 24 строк из таблицы product_description, отсортированных по name, то ведь тут нету эквивалентности моему запросу -в category_id=4 могут оказаться только продукты чьи имена начинаются с буквы "P", а из таблицы product_description будут выбраны только продукты с именами, начинающимися с первых/последних букв алфавита -соответственно полный запрос вернёт 0 строк..
Скорее всего я просто не понял вашего сообщения -напишите, пожалуйста, запрос

Отредактированно jerry (02.02.2011 13:57:19)

paulus · 02.02.2011 14:52:09

SELECT x.* FROM product x JOIN
(SELECT id
FROM product p
JOIN product_description pd ON ( p.product_id = pd.product_id )
JOIN product_to_category p2c ON ( p.product_id = p2c.product_id )
WHERE p.status = '1'
AND p.date_available <= NOW( )
AND pd.language_id = '1'
AND p2c.category_id = '4'
ORDER BY pd.name DESC
LIMIT 0 , 24) s USING (id) ?

jerry · 05.02.2011 19:21:05

Прошу прощения за отсутствие

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

SELECT x.* FROM product x JOIN

(SELECT p.product_id

FROM product p

JOIN product_description pd ON ( p.product_id = pd.product_id )

JOIN product_to_category p2c ON ( p.product_id = p2c.product_id )

WHERE p.status = '1'

AND p.date_available <= NOW( )

AND pd.language_id = '1'

AND p2c.category_id = '4'

ORDER BY pd.name DESC

LIMIT 0 , 24) s USING (product_id)

так получается быстрее примерно раз в 5. Я ещё посмотрю, но возможно всё-таки введу в таблицу в product_to_category дополнителное поле name с индексом и буду сортировать по нему.

Отредактированно jerry (05.02.2011 19:21:26)

Форум пользователей MySQL

#1 31.01.2011 21:00:25

LEFT JOIN и сортировка

#2 31.01.2011 21:47:48

Re: LEFT JOIN и сортировка

#3 31.01.2011 22:22:26

Re: LEFT JOIN и сортировка

#4 01.02.2011 15:00:04

Re: LEFT JOIN и сортировка

#5 01.02.2011 22:56:29

Re: LEFT JOIN и сортировка

#6 01.02.2011 23:13:39

Re: LEFT JOIN и сортировка

#7 02.02.2011 00:11:05

Re: LEFT JOIN и сортировка

#8 02.02.2011 00:19:12

Re: LEFT JOIN и сортировка

#9 02.02.2011 01:11:32

Re: LEFT JOIN и сортировка

#10 02.02.2011 13:27:05

Re: LEFT JOIN и сортировка

#11 02.02.2011 14:52:09

Re: LEFT JOIN и сортировка

#12 05.02.2011 19:21:05

Re: LEFT JOIN и сортировка

Board footer