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

plazmagod · 28.06.2011 11:55:12

Есть три таблички:
1. Таблица параметров товара.(Режим, Мощность, Вес) - item_params (id, class_id, name)
Где class_id - нужный мне вид товара.
2. Таблица значений параметров товара. (Холод и тепло, 5КВт, 15кг) - item_values (item_id, param_id, value)
3. Таблица самого товара. - items (id, name,class)

Делаю:

SELECT items.name

FROM items

WHERE items.id

IN (

SELECT item_id

FROM item_values

WHERE value = "Холод и тепло"

AND param_id

IN (

SELECT `item_params`.`id`

FROM `item_params`

WHERE class_id

IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 )

AND `item_params`.`name` = 'Режим'

)

)

Запрос на локальной машине занимает 12 секунд.

Как лучше оптимизировать данный запрос?

UPD: EXPLAIN по данному запросу:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY items ALL NULL NULL NULL NULL 3335 Using where; Using filesort
2 DEPENDENT SUBQUERY item_values ALL NULL NULL NULL NULL 6495 Using where
3 DEPENDENT SUBQUERY item_params ALL NULL NULL NULL NULL 364 Using where

Отредактированно plazmagod (28.06.2011 14:31:38)

seekwell · 28.06.2011 13:40:02

Попробуй так:

SELECT

  i.name

FROM

  items i

  JOIN item_values iv ON i.id = iv.item_id AND

                         iv.value = "Холод и тепло"

  JOIN item_params ip ON ip.param_id = iv.param_id AND

                          ip.class_id BETWEEN 1 AND 12 AND

                          ip.name = 'Режим'

;

paulus · 28.06.2011 14:15:00

И EXPLAIN от запроса после замены

plazmagod · 28.06.2011 14:23:53

seekwell написал:
Попробуй так:

SELECT
i.name
FROM
items i
JOIN item_values iv ON i.id = iv.item_id AND
iv.value = "Холод и тепло"
JOIN item_params ip ON ip.param_id = iv.param_id AND
ip.class_id BETWEEN 1 AND 12 AND
ip.name = 'Режим'
;

Красиво.

Выдаёт 900 повторяющихся строк. Делаю DISTINCT, всё вроде верно, 75 строк.

SELECT DISTINCT i.name

FROM items i

JOIN item_values iv ON i.id = iv.item_id

AND iv.value = "Холод и тепло"

JOIN item_params ip ON ip.id = iv.param_id

AND ip.class_id

BETWEEN 1

AND 12

AND ip.name = 'Режим'

Вопрос ещё такой, если по нескольким параметрам (ip.name), сравнивая значения (iv.value) выбирать, то как лучше собирать запрос?

UPD: EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ip ALL NULL NULL NULL NULL 364 Using where; Using temporary
1 SIMPLE i ALL NULL NULL NULL NULL 3335 Using join buffer
1 SIMPLE iv ALL NULL NULL NULL NULL 6495 Using where; Distinct; Using join buffer

Отредактированно plazmagod (28.06.2011 14:26:25)

paulus · 28.06.2011 18:11:29

А сколько строк выдает Ваш запрос? Неужели, другое?

plazmagod · 29.06.2011 08:59:30

paulus написал:
А сколько строк выдает Ваш запрос? Неужели, другое?

75 без DISTINCT. Но он настолько медленный, что использовать его совсем невозможно. 15-17 сек. для одного запроса на локальной машине - совсем никуда.

Кстати, я выше ему EXPLAIN подвесил. Можете на пальцах объяснить почему он долгий такой?

Отредактированно plazmagod (29.06.2011 09:01:16)

DanJer · 29.06.2011 10:19:20

Попробуйте сделать таблицы временные через CREATE TEMPORARY TABLE и обязательно проиндексируйте поля, по которым идет JOIN

plazmagod · 29.06.2011 11:09:53

DanJer написал:
Попробуйте сделать таблицы временные через CREATE TEMPORARY TABLE и обязательно проиндексируйте поля, по которым идет JOIN

Спасибо. С этим

SELECT DISTINCT i.name

FROM items i

JOIN item_values iv ON i.id = iv.item_id

AND iv.value = "Холод и тепло"

JOIN item_params ip ON ip.id = iv.param_id

AND ip.class_id

BETWEEN 1

AND 12

AND ip.name = 'Режим'

запросом меня сейчас всё устраивает и с DISTINCT. Он реально быстрый после добавления индекса полю id и последующей optimize.

Сейчас EXPLAIN выглядит так:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ip ALL NULL NULL NULL NULL 364 Using where; Using temporary
1 SIMPLE iv ALL NULL NULL NULL NULL 6495 Using where; Using join buffer
1 SIMPLE i eq_ref PRIMARY,id PRIMARY 4 udb64.iv.item_id 1

Хотя если подскажете, как использовать временную таблицу в этом случае более эффективно, буду признателен.

evgeny · 29.06.2011 11:35:57

Давай те для начала посмотрим структуру ваших таблиц

SHOW CREATE TABLE  items;

SHOW TABLE  STATUS LIKE 'items';

SHOW CREATE TABLE  item_values;

SHOW TABLE  STATUS LIKE 'item_values';

SHOW CREATE TABLE  item_params;

SHOW TABLE  STATUS LIKE 'item_params';

plazmagod · 29.06.2011 12:25:49

Таблица items:

CREATE TABLE `items` (

 `id` int(11) NOT NULL,

 `name` tinytext NOT NULL,

 `class` int(11) DEFAULT NULL,

 `manuf_id` int(11) DEFAULT NULL,

 `short_desc` text,

 `long_desc` text,

 `img` tinytext NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Name items
Engine MyISAM
Version 10
Row_format Dynamic
Rows 3319
Avg_row_length 868
Data_length 2881460
Max_data_length 281474976710655
Index_length 41984
Data_free 0
Auto_increment NULL
Create_time 2011-06-29 11:57:47
Update_time 2011-06-29 11:57:47
Check_time 2011-06-29 11:58:03
Collation utf8_general_ci
Checksum NULL
Create_options
Comment

Таблица item_values:

CREATE TABLE `item_values` (

 `item_id` int(11) NOT NULL DEFAULT '0',

 `param_id` int(11) NOT NULL DEFAULT '0',

 `value` text NOT NULL,

 KEY `item_id` (`item_id`,`param_id`),

 FULLTEXT KEY `value` (`value`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Name item_values
Engine MyISAM
Version 10
Row_format Dynamic
Rows 6495
Avg_row_length 24
Data_length 160684
Max_data_length 281474976710655
Index_length 152576
Data_free 0
Auto_increment NULL
Create_time 29/6/2011
Update_time 29/6/2011
Check_time 29/6/2011
Collation utf8_general_ci
Checksum NULL
Create_options
Comment

Таблица item_params:

CREATE TABLE `item_params` (

 `id` int(11) NOT NULL,

 `class_id` int(11) NOT NULL,

 `name` text NOT NULL,

 KEY `id` (`id`,`class_id`),

 FULLTEXT KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Name item_params
Engine MyISAM
Version 10
Row_format Dynamic
Rows 364
Avg_row_length 46
Data_length 17060
Max_data_length 281474976710655
Index_length 18432
Data_free 0
Auto_increment NULL
Create_time 29/6/2011
Update_time 29/6/2011
Check_time 29/6/2011
Collation utf8_general_ci
Checksum NULL
Create_options
Comment

Отредактированно plazmagod (29.06.2011 12:43:29)

evgeny · 29.06.2011 15:04:54

Если ничего не напутал то должно быть так:

SELECT DISTINCT(i.name) FROM items i

LEFT JOIN item_values iv ON (i.id = iv.item_id AND iv.value = "Холод и тепло")

LEFT JOIN item_params ip ON (iv.param_id=ip.id AND (ip.class_id BETWEEN 1 AND 12) AND ip.name = 'Режим');

индексы

(i.name)

(iv.item_id,iv.value)

(ip.id, ip.class_id, ip.name)

plazmagod · 29.06.2011 15:22:26

А какие отличия от кода предложенного seekwell?!

seekwell написал:
Попробуй так:

SELECT
i.name
FROM
items i
JOIN item_values iv ON i.id = iv.item_id AND
iv.value = "Холод и тепло"
JOIN item_params ip ON ip.param_id = iv.param_id AND
ip.class_id BETWEEN 1 AND 12 AND
ip.name = 'Режим';

Ещё вопрос. Параметров может быть несколько: "Режим", "Вес", "Мощность". Значения их соответственно будут тоже разнотипными: "Холод и тепло", "15кг", "5КВт". Как в случае такого синтаксиса сделать запрос, сравнивающий в случае "Режим" текстовую строку, а в случае "Вес" и "Мощность" отсекали бы из string integer и сравнивали бы уже числом по величине?

Может действительно в этом случае временные таблицы были бы удобнее?

evgeny · 29.06.2011 15:59:35

plazmagod написал:
А какие отличия от кода предложенного seekwell?!

Индексы.

plazmagod написал:
Ещё вопрос. Параметров может быть несколько: "Режим", "Вес", "Мощность". Значения их соответственно будут тоже разнотипными: "Холод и тепло", "15кг", "5КВт". Как в случае такого синтаксиса сделать запрос, сравнивающий в случае "Режим" текстовую строку, а в случае "Вес" и "Мощность" отсекали бы из string integer и сравнивали бы уже числом по величине?
Может действительно в этом случае временные таблицы были бы удобнее?

Не нужны вам временные таблицы, поставте индексы которые я вам написал и прешлите новый EXPLAIN.

Ещё зачем у вас стоит FULLTEXT KEY ? Бывают случаи когда вы ищите отдельные слова в тексте ? Если нет, то снимите его.

plazmagod написал:
"Режим", "Вес", "Мощность", "Холод и тепло", "15кг", "5КВт".

Почему вообще идёт запрос по словам а не изначально по id ?

У вас что в самом приложении на клиенте так обозначаются поля ?

Что то типо

<select>

<option value="Режим"></option>

<option value="Вес"></option>

<option value="Мощность"></option>

</select>

?

plazmagod · 29.06.2011 16:42:19

поставте индексы которые я вам написал и прешлите новый EXPLAIN.

Проставил. Поля TEXT и TINYTEXT преобразовал в CHAR. Запрос выдал вообще все i.name таблицы items

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ALL NULL NULL NULL NULL 3319 Using temporary
1 SIMPLE iv ref item_id_2 item_id_2 64 udb64.i.id,const 1 Distinct
1 SIMPLE ip ref id_2 id_2 4 udb64.iv.param_id 30 Using index; Distinct

Работающий код из #8 коммента выдал EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ALL PRIMARY NULL NULL NULL 3319 Using temporary
1 SIMPLE iv ref item_id_2 item_id_2 64 udb64.i.id,const 1 Using where; Distinct
1 SIMPLE ip ref id_2 id_2 4 udb64.iv.param_id 30 Using where; Using index; Distinct

FULLTEXT снял.

Почему вообще идёт запрос по словам а не изначально по id ?

C 1C в базу сайта характеристики объекта выгружается в виде произвольной пачки. Ни разделения по классам, ни унификации. XML-файл товаров примерно похож на этот select.

evgeny · 29.06.2011 18:00:31

Запрос выдал вообще все i.name таблицы items

И это означает что в вашем запросе точно нету DISTINCT-а

SELECT DISTINCT i.name
FROM items i
JOIN item_values iv ON i.id = iv.item_id
AND iv.value = "Холод и тепло"
JOIN item_params ip ON ip.id = iv.param_id
AND ip.class_id
BETWEEN 1
AND 12
AND ip.name = 'Режим'

+

Индексы
(i.name)
(iv.item_id,iv.value)
(ip.id, ip.class_id, ip.name)

Запрос и структуру в студию:

SHOW CREATE TABLE items;
SHOW CREATE TABLE item_values;
SHOW CREATE TABLE item_params;

Отредактированно evgeny (29.06.2011 18:01:03)

plazmagod · 30.06.2011 10:23:28

Вот этот запрос нормально отработал. Его EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ALL PRIMARY NULL NULL NULL 3319 Using temporary
1 SIMPLE iv ref item_id_2 item_id_2 64 udb64.i.id,const 1 Using where; Distinct
1 SIMPLE ip ref id_2 id_2 4 udb64.iv.param_id 30 Using where; Using index; Distinct

Таблица items:

CREATE TABLE `items` (

 `id` int(11) NOT NULL,

 `name` char(50) NOT NULL,

 `class` int(11) DEFAULT NULL,

 `manuf_id` int(11) DEFAULT NULL,

 `short_desc` text,

 `long_desc` text,

 `img` tinytext NOT NULL,

 PRIMARY KEY (`id`),

 KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Таблица item_values:

CREATE TABLE `item_values` (

 `item_id` int(11) NOT NULL DEFAULT '0',

 `param_id` int(11) NOT NULL DEFAULT '0',

 `value` char(20) NOT NULL,

 KEY `item_id_2` (`item_id`,`value`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Таблица item_params:

CREATE TABLE `item_params` (

 `id` int(11) NOT NULL,

 `class_id` int(11) NOT NULL,

 `name` char(20) NOT NULL,

 KEY `id_2` (`id`,`class_id`,`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

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

#1 28.06.2011 11:55:12

Вложенные запросы. Тормоза.

#2 28.06.2011 13:40:02

Re: Вложенные запросы. Тормоза.

#3 28.06.2011 14:15:00

Re: Вложенные запросы. Тормоза.

#4 28.06.2011 14:23:53

Re: Вложенные запросы. Тормоза.

seekwell написал:

#5 28.06.2011 18:11:29

Re: Вложенные запросы. Тормоза.

#6 29.06.2011 08:59:30

Re: Вложенные запросы. Тормоза.

paulus написал:

#7 29.06.2011 10:19:20

Re: Вложенные запросы. Тормоза.

#8 29.06.2011 11:09:53

Re: Вложенные запросы. Тормоза.

DanJer написал:

#9 29.06.2011 11:35:57

Re: Вложенные запросы. Тормоза.

#10 29.06.2011 12:25:49

Re: Вложенные запросы. Тормоза.

#11 29.06.2011 15:04:54

Re: Вложенные запросы. Тормоза.

#12 29.06.2011 15:22:26

Re: Вложенные запросы. Тормоза.

seekwell написал:

#13 29.06.2011 15:59:35

Re: Вложенные запросы. Тормоза.

plazmagod написал:

plazmagod написал:

plazmagod написал:

#14 29.06.2011 16:42:19

Re: Вложенные запросы. Тормоза.

#15 29.06.2011 18:00:31

Re: Вложенные запросы. Тормоза.

#16 30.06.2011 10:23:28

Re: Вложенные запросы. Тормоза.

Board footer