Модель EAV (сущность - характеристики) / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Модель EAV (сущность - характеристики)

53 сообщений из 53, показаны все 3 страниц

все

Модель EAV (сущность - характеристики)

#38527387

folder-pro

Гость

Доброго времени суток уважаемые форумчане.

Прошу помощи в доработке EAV.

Имеем упрощенную таблицу основных записей.

упрощенная схема хранения

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

SELECT
	jos_md_catalog_message_criterions.id,
	jos_md_catalog_message_criterions.message_id,
	jos_md_catalog_criterions_name.`name` AS param_name,
	jos_md_catalog_criterions_type.`name` AS type_name,
	jos_md_catalog_criterions_values.`values`
FROM
	jos_md_catalog_message_criterions
LEFT JOIN jos_md_catalog_criterions_name ON jos_md_catalog_message_criterions.id_criterions_name = jos_md_catalog_criterions_name.id
LEFT JOIN jos_md_catalog_criterions_type ON jos_md_catalog_criterions_name.id_type = jos_md_catalog_criterions_type.id
LEFT JOIN jos_md_catalog_criterions_values ON jos_md_catalog_message_criterions.id_criterions_values = jos_md_catalog_criterions_values.id

В чистом виде данные характеристик

[img] http://clip2net.com/clip/m0/1389792562-clip-7kb.png?nocache=1 [/img]

Теперь собственно проблема, необходимо выбрать нужные ID при поиске по этой таблице.
Делаем запрос

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SELECT
	message_id
FROM
	jos_md_catalog_message_criterions
WHERE
	(
		(
			jos_md_catalog_message_criterions.`id_criterions_name` = '4'
			AND jos_md_catalog_message_criterions.`id_criterions_values` = '13'
		)
		OR (
			jos_md_catalog_message_criterions.`id_criterions_name` = '25'
			AND jos_md_catalog_message_criterions.`id_criterions_values` = '95'
		)
	)

пытаясь найти записи у которых есть характеристика 4 с ID значения 13
и 25 с ID значения 95

Этот запрос работает, но он работает на расширение поиска а не на сужение.
То есть чем больше галочек в фильтре поставить, тем больше блоков OR будет добавлено и в выборку попадет большее количество записей.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SELECT
	message_id
FROM
	jos_md_catalog_message_criterions
WHERE
	(
		(
			jos_md_catalog_message_criterions.`id_criterions_name` = '4'
			AND jos_md_catalog_message_criterions.`id_criterions_values` = '13'
		)
		AND (
			jos_md_catalog_message_criterions.`id_criterions_name` = '25'
			AND jos_md_catalog_message_criterions.`id_criterions_values` = '95'
		)
	)

Если же поставить между двумя выборками характеристик AND естествено ничего не найдется.
Собственно в этом и вопрос, как построить запрос с обратной логикой поиска, чем больше характеристик в запросе, тем больше сужается выдача.

Заранее благодарен за любую помощь.

...

Рейтинг:

0 / 0

15.01.2014, 18:07:58

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527391

folder-pro

Гость

Забыл дамп прикрепить

...

Рейтинг:

0 / 0

15.01.2014, 18:09:40

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527401

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

folder-proпытаясь найти записи у которых есть характеристика 4 с ID значения 13 и 25 с ID значения 95
Да щазз!

Шаблон:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SELECT entity
FROM eav_table
GROUP BY entity
HAVING av_pairs_count = SUM(
( (attribute, value) = (att_1, val_1) )
OR
( (attribute, value) = (att_2, val_2) )
OR
....
)

...

Рейтинг:

0 / 0

15.01.2014, 18:19:14

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527409

folder-pro

Гость

Akina,

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

...

Рейтинг:

0 / 0

15.01.2014, 18:24:50

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527412

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

AkinaШаблон:Да уж больно медленный шаблон-то...

...

Рейтинг:

0 / 0

15.01.2014, 18:26:48

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527431

folder-pro

Гость

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SELECT
	jos_md_catalog_message_criterions.message_id
FROM
	jos_md_catalog_message_criterions
GROUP BY
	jos_md_catalog_message_criterions.message_id
HAVING
	2 = SUM(
		(
			(
				jos_md_catalog_message_criterions.id_criterions_name,
				jos_md_catalog_message_criterions.id_criterions_values
			) = (4, 13)
		)
		OR (
			(
				jos_md_catalog_message_criterions.id_criterions_name,
				jos_md_catalog_message_criterions.id_criterions_values
			) = (25, 95)
		)
	)

...

Рейтинг:

0 / 0

15.01.2014, 18:44:26

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527434

folder-pro

Гость

Akinafolder-proпытаясь найти записи у которых есть характеристика 4 с ID значения 13 и 25 с ID значения 95
Да щазз!

Шаблон:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SELECT entity
FROM eav_table
GROUP BY entity
HAVING av_pairs_count = SUM(
( (attribute, value) = (att_1, val_1) )
OR
( (attribute, value) = (att_2, val_2) )
OR
....
)

непонятно что за поле av_pairs_count и что выдаст запрос...

...

Рейтинг:

0 / 0

15.01.2014, 18:45:22

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527436

folder-pro

Гость

folder-pro

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SELECT
	jos_md_catalog_message_criterions.message_id
FROM
	jos_md_catalog_message_criterions
GROUP BY
	jos_md_catalog_message_criterions.message_id
HAVING
	2 = SUM(
		(
			(
				jos_md_catalog_message_criterions.id_criterions_name,
				jos_md_catalog_message_criterions.id_criterions_values
			) = (4, 13)
		)
		OR (
			(
				jos_md_catalog_message_criterions.id_criterions_name,
				jos_md_catalog_message_criterions.id_criterions_values
			) = (25, 95)
		)
	)

как доработать его, чтобы заработало, сейчас выдает null

вместо av_pairs_count подставлено 2

...

Рейтинг:

0 / 0

15.01.2014, 18:48:29

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527437

folder-pro

Гость

miksoft,

Вы предлагаете на каждый тип товара отдельную таблицу?

скажем 500 типов товаров, и 500+ таблиц где будут храниться характеристики?

...

Рейтинг:

0 / 0

15.01.2014, 18:49:43

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527445

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

folder-promiksoft,

Вы предлагаете на каждый тип товара отдельную таблицу?

скажем 500 типов товаров, и 500+ таблиц где будут храниться характеристики?В данный момент не предлагаю. Чтобы предлагать такие вещи, нужно очень хорошо знать задачу, что малореально в формате форума. Хотя, теоретически, и в 500 таблицах ничего страшного не вижу.

Я говорил и именно о самом запросе в рамках текущей ситуации. Уж хотя бы WHERE можно было бы добавить.

...

Рейтинг:

0 / 0

15.01.2014, 18:57:48

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527452

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

miksoftAkinaШаблон:Да уж больно медленный шаблон-то...
Конечно, это же идея, а не реализация.

...

Рейтинг:

0 / 0

15.01.2014, 19:04:26

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527521

folder-pro

Гость

Akinamiksoftпропущено...
Да уж больно медленный шаблон-то...
Конечно, это же идея, а не реализация.

Если предложили этот вариант, может объясните его суть? ибо вопрос не решен пока что...

...

Рейтинг:

0 / 0

15.01.2014, 20:51:34

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527527

folder-pro

Гость

miksoft,

Суть проекта.
есть доска объявлений - каталог товаров, у объявлений есть характеристики, у каждого раздела свои, "по типу яндекс маркета".
фильтров будет не мало, думаю до 500 шт.

Изначально думал использовать вариант 1 тип - 1 таблица хранения характеристик, с редактором характеристик который меняет таблицу.
Но плодить большое количество таблиц как то показалось страшным.

записей основной таблицы будет в районе 500 000 шт.
если предположить что у каждого товара будет по 16 характеристик, то таблица jos_md_catalog_message_criterions будет содержать порядка 8 000 000 записей.

что в такой ситуации вы могли бы предложить?

...

Рейтинг:

0 / 0

15.01.2014, 20:59:42

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527531

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Так Вы же суть поймали и составили в общем верный запрос...

Код: 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.

mysql> create table eav(entity int, attrib int, val int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into eav (entity, attrib, val)
    -> select 1, 1, 1 union
    -> select 1, 2, 2 union
    -> select 2, 1, 1 union
    -> select 2, 2, 1 union
    -> select 3, 1, 2 union
    -> select 3, 2, 2 ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from eav;
+--------+--------+------+
| entity | attrib | val  |
+--------+--------+------+
|      1 |      1 |    1 |
|      1 |      2 |    2 |
|      2 |      1 |    1 |
|      2 |      2 |    1 |
|      3 |      1 |    2 |
|      3 |      2 |    2 |
+--------+--------+------+
6 rows in set (0.00 sec)

mysql> select entity
    -> from eav
    -> group by entity
    -> having sum( (attrib, val) in ( (1,1), (2,2) ) );
+--------+
| entity |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.03 sec)

mysql> select entity
    -> from eav
    -> group by entity
    -> having 2 = sum( (attrib, val) in ( (1,1), (2,2) ) );
+--------+
| entity |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> select entity
    -> from eav
    -> group by entity
    -> having 1 = sum( (attrib, val) in ( (1,1) ) );
+--------+
| entity |
+--------+
|      1 |
|      2 |
+--------+
2 rows in set (0.00 sec)

mysql>

Если запрос даёт NULL - либо нет запрошенных данных, либо есть дубликаты, либо ты в запросе таки накосячил.

...

Рейтинг:

0 / 0

15.01.2014, 21:10:29

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527539

folder-pro

Гость

Akina,

нет не накосячил, я просто не мог его суть понять и что там делает av_pairs_count
теперь суть понятна, спасибо.

У вас под рукой нет случайно статей на манипуляцию с этим шаблоном?
Или может быть ссыль на книгу "на русском", где подобная техника более подробно описывается?

из того что нагуглил, особо разобраться не смог (

...

Рейтинг:

0 / 0

15.01.2014, 21:20:47

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527541

folder-pro

Гость

Akina,

да и IN откуда взялось, в первом ответе было другое )

...

Рейтинг:

0 / 0

15.01.2014, 21:22:59

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527569

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Akinafolder-proпытаясь найти записи у которых есть характеристика 4 с ID значения 13 и 25 с ID значения 95
Да щазз!

Шаблон:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

SELECT entity
FROM eav_table
GROUP BY entity
HAVING av_pairs_count = SUM(
( (attribute, value) = (att_1, val_1) )
OR
( (attribute, value) = (att_2, val_2) )
OR
....
)

Лучше всё же

Код: sql

1.
2.
3.
4.
5.

SELECT entity
FROM eav_table e
where exists ( select * from attribure a where e.entity_id = a.entity_id and a.attribute = 'att1' and a.value = @val_1 )
   and exists ( select * from attribure a where e.entity_id = a.entity_id and a.attribute = 'att2' and a.value = @val_2 )
-- ...

...

Рейтинг:

0 / 0

15.01.2014, 21:58:26

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527635

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

folder-proУ вас под рукой нет случайно статей на манипуляцию с этим шаблоном?Да я его прямо тут от руки набросал... какие нахрен статьи ещё. Потому, кстати, и дорабатываю, в частности
folder-proда и IN откуда взялось, в первом ответе было другое )
это по сути абсолютно то же самое, просто запись более читабельная.

MasterZivЛучше всё же
Тогда уж

Код: sql

... exists ( select 1 from ...

Но мой шаблон гораздо удобнее при неопределённом количестве пар... а если начнётся огород типа "эти две пары обязательно, из вон тех пяти пар должно быть не менее трёх, а этих двух быть не должно", то он вообще становится чертовски привлекательным....

К слову, для моего шаблона, если в таблице есть подходящий индекс (в коем поля идут в правильном порядке) - он будет использоваться.

...

Рейтинг:

0 / 0

15.01.2014, 23:47:18

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38527984

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

MasterZivЛучше всё же
Тогда уж

Код: sql

... exists ( select 1 from ...

разницы 0.

Но мой шаблон гораздо удобнее при неопределённом количестве пар... а если

не думаю.

...

Рейтинг:

0 / 0

16.01.2014, 12:12:40

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528034

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

16.01.2014, 12:33:07

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528140

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

Akina MasterZiv , мне во всяком случае было бы удобнее,

Согласись, это далеко не самый важный критерий оценки текста запроса.

...

Рейтинг:

0 / 0

16.01.2014, 13:22:25

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528197

folder-pro

Гость

Akinafolder-proУ вас под рукой нет случайно статей на манипуляцию с этим шаблоном?Да я его прямо тут от руки набросал... какие нахрен статьи ещё. Потому, кстати, и дорабатываю, в частности
folder-proда и IN откуда взялось, в первом ответе было другое )
это по сути абсолютно то же самое, просто запись более читабельная.

MasterZivЛучше всё же
Тогда уж

Код: sql

... exists ( select 1 from ...

подскажите как их правильно создать тогда, индексы

...

Рейтинг:

0 / 0

16.01.2014, 13:47:49

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528236

folder-pro

Гость

Akina,

...

Рейтинг:

0 / 0

16.01.2014, 14:07:37

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528278

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

folder-proсгенерил таблицу на 11000 характеристик для 2000 товаров, ваш запрос не желает индексы использовать...
Ага... possible keys IS NULL - а где подходящий индекс-то? трудно использовать то, чего нет.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

mysql> create index idx1 on eav(entity, attrib, val);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain
    -> select entity
    -> from eav
    -> group by entity
    -> having 2 = sum( (attrib, val) in ( (1,1), (2,2) ) );
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | eav   | index | idx1          | idx1 | 15      | NULL |    1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

...

Рейтинг:

0 / 0

16.01.2014, 14:28:40

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528319

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

http://sqlfiddle.com/#!2/5e97c/1

Код: sql

KEY id_nm_val (`message_id`,`id_criterions_name`,`id_criterions_values`)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT
	message_id
FROM
	jos_md_catalog_message_criterions
WHERE 
  (`id_criterions_name`,`id_criterions_values`) in (
    (4,13),(25,93)
  )
group by message_id
having sum(
  (`id_criterions_name`,`id_criterions_values`) in (
    (4,13),(25,93)
  )
  )=2;

ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS FILTERED EXTRA1SIMPLEjos_md_catalog_message_criterionsindexid_nm_val154100Using where; Using index

...

Рейтинг:

0 / 0

16.01.2014, 14:44:39

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528364

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

или даже так: ( http://sqlfiddle.com/#!2/a830f/1 )

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

create temporary table tmp (id_nm int, id_val int);
insert tmp values (4,13),(25,93);

SELECT message_id
FROM jos_md_catalog_message_criterions
join tmp t on t.id_nm=`id_criterions_name` and t.id_val=`id_criterions_values`
group by message_id
having count(*)=(select count(*) from tmp);

drop temporary table tmp;

...

Рейтинг:

0 / 0

16.01.2014, 14:58:25

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528500

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

сгенерил таблицу на 11000 характеристик для 2000 товаров, ваш запрос не желает индексы использовать...

А ты видишь в этом запросе слово WHERE ?
Запросы используют индексы, когда в WHERE что-то написано.

Я ж ему говорил, что так лучше. А он -- "удобно, удобно "

подскажите как их правильно создать тогда, индексы

Надо не индексы правильно создавать, а запросы правильно писать.
Индексы надо создавать на значение и тип свойства .

...

Рейтинг:

0 / 0

16.01.2014, 16:21:14

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528632

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

Уточнение: защита от дублей (предполагается, что фильтруемые значения выбираются однократно)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

create temporary table tmp (id int auto_increment primary key, id_nm int, id_val int);
insert tmp (id_nm, id_val) values (4,13),(25,93);

SELECT message_id
FROM jos_md_catalog_message_criterions
join tmp t on t.id_nm=`id_criterions_name` and t.id_val=`id_criterions_values`
group by message_id
having count(distinct t.id)=(select count(*) from tmp);

drop temporary table tmp;

...

Рейтинг:

0 / 0

16.01.2014, 17:40:32

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528669

folder-pro

Гость

Cygapb-007,

да индексы на этом вложенном запросе заработали.
но зависание на 62 секунды так и осталось)

...

Рейтинг:

0 / 0

16.01.2014, 17:59:49

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528929

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

folder-pro ,

вам уже отвечали ранее 15425584 , что индексы не работают после группировки.

попробуйте добавить фильтр в запрос (перед группировкой)

...

Рейтинг:

0 / 0

16.01.2014, 22:02:23

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528933

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

можете так же сравнить обведенное рамкой с примером из 15424973

...

Рейтинг:

0 / 0

16.01.2014, 22:06:03

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528974

folder-pro

Гость

В общем наконец то заработало.
Не идеально конечно, но уже хоть что то.

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

например если взять основную таблицу в которой например будет 250 000 записей, и по 10 характеристик на один товар, получим перебор таблицы в которой 2 500 000 записей и джойн ее хоть и по PK к таблице в которой 250 000 записей. при этом еще и Using temporary; Using filesort

...

Рейтинг:

0 / 0

16.01.2014, 22:40:37

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528982

folder-pro

Гость

Cygapb-007можете так же сравнить обведенное рамкой с примером из 15424973

Да да конечно я это заметил.

WHERE
(`id_criterions_name`,`id_criterions_values`) in (
(4,13),(25,93)
)

но вот кроме добавленного в эксплайн Using where; Using index

вместо Using index без этого предиката, я разницы пока не увидел или не догнал в чем она...
что там что там в строке rows 11500 записей, и для примера выше там будет 2 500 000...
и как с этим жить пока не понимаю.
php/.NET/VB я программист, а не SQL гуру, посему мне трудно дается это, прошу пояснить если не сложно.

...

Рейтинг:

0 / 0

16.01.2014, 22:45:47

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38528992

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

еще раз:

группировка обрабатывает все отобранные записи таблицы (сортирует, затем вычисляет аггрегатные функции), и только потом проверяет вычисленные значения.

фильтр WHERE использует индекс для эффективного отбора значений, которые потом нужно как-то обработать.

Хотя если не видно разницы - то и мне и тем более все равно

...

Рейтинг:

0 / 0

16.01.2014, 22:55:19

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529008

folder-pro

Гость

Cygapb-007еще раз:

группировка обрабатывает все отобранные записи таблицы (сортирует, затем вычисляет аггрегатные функции), и только потом проверяет вычисленные значения.

фильтр WHERE использует индекс для эффективного отбора значений, которые потом нужно как-то обработать.

Хотя если не видно разницы - то и мне и тем более все равно

Другими словами, разница будет видна в скорости работы запроса когда там будет 2 500 000 записей? а не 12 000.

я понимаю что есть разница с where и без него, только вот не понимаю меняется ли последовательность обработки этого запроса или нет, и как это в эксплайн увидеть... Не вижу я ее :)

...

Рейтинг:

0 / 0

16.01.2014, 23:20:33

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529123

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

folder-proНе вижу я ее :)explain.rows должно уменьшаться при наличии where
Впрочем, у ва всего 12к записей, может, оптимизатор посчитал выгодней прочитать их все, не дёргая ещё и индекс. Сделайте 120к записей и повторите эксперимент - rows должно отличаться.
Akina

Код: sql

create index idx1 on eav(entity, attrib, val);

Если разных атрибутов будет много, то может быть, выгодней будет idx1 on eav(attrib, val, entity). Можно будет читать даже не весь индекс, а только часть.

...

Рейтинг:

0 / 0

17.01.2014, 06:21:10

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529142

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

tanglirЕсли разных атрибутов будет много, то может быть, выгодней будет idx1 on eav(attrib, val, entity). Можно будет читать даже не весь индекс, а только часть.Я демонстрирорвал только факт, что индекс МОЖЕТ ИСПОЛЬЗОВАТЬСЯ. Оптимизация его использования - это значительно более следующий этап.

...

Рейтинг:

0 / 0

17.01.2014, 08:50:30

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529168

folder-pro

Гость

tanglirfolder-proНе вижу я ее :)explain.rows должно уменьшаться при наличии where
Впрочем, у ва всего 12к записей, может, оптимизатор посчитал выгодней прочитать их все, не дёргая ещё и индекс. Сделайте 120к записей и повторите эксперимент - rows должно отличаться.
Akina

Код: sql

create index idx1 on eav(entity, attrib, val);

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

Огромное всем спасибо за помощь!

...

Рейтинг:

0 / 0

17.01.2014, 09:58:54

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529176

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

folder-pro, ну всё-таки попробуйте (name,value,id). Только не на 12к записей. Хотя если name достаточно редкое, то можно и так попробовать.

...

Рейтинг:

0 / 0

17.01.2014, 10:06:47

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529182

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

tanglirfolder-pro, ну всё-таки попробуйте (name,value,id). Только не на 12к записей. Хотя если name достаточно редкое, то можно и так попробовать.группировка предполагает предварительную сортировку по id, поэтому вряд ли id в конце индекса будет эффективнее... хотя решать оптимизатору

...

Рейтинг:

0 / 0

17.01.2014, 10:10:35

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529196

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

Cygapb-007,

этот индекс - для случая, когда where отбирает относительно мало дынных. Проще ведь прочитать 100 записей из индекса, чем весь индекс :)

...

Рейтинг:

0 / 0

17.01.2014, 10:23:50

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529413

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

tanglirCygapb-007,

этот индекс - для случая, когда where отбирает относительно мало дынных. Проще ведь прочитать 100 записей из индекса, чем весь индекс :)А, в смысле - покрывающий индекс (on (name, val) include (mess_id))... Согласен, этот индекс может оказаться эффективнее.

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

...

Рейтинг:

0 / 0

17.01.2014, 12:43:35

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529417

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

Cygapb-007, может. Тут уже ТСу самому смотреть надо.

...

Рейтинг:

0 / 0

17.01.2014, 12:45:24

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529791

folder-pro

Гость

tanglir,

догенерил данных до 14к товаров
и 69к характеристик

...

Рейтинг:

0 / 0

17.01.2014, 15:47:39

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529803

folder-pro

Гость

tanglirfolder-pro, ну всё-таки попробуйте (name,value,id). Только не на 12к записей. Хотя если name достаточно редкое, то можно и так попробовать.

удалил все индексы

создал
id, id_criterions_name, id_criterions_values

вместо 0,047 сек
стало 0,015 сек

в rows остались теже 69000
к тому же Using where; Using temporary; Using filesort
вместо Using where; Using index;

...

Рейтинг:

0 / 0

17.01.2014, 15:54:38

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529838

folder-pro

Гость

Cygapb-007,

еще бы синтаксис знать того что вы предложили...

ALTER TABLE `jos_md_catalog_message_criterions` ADD INDEX
`ggg` USING BTREE ( ON ( `id_criterions_name`, `id_criterions_values` ) include(message_id) )

в чем ошибка тут?

...

Рейтинг:

0 / 0

17.01.2014, 16:15:17

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529847

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

folder-proCygapb-007,

еще бы синтаксис знать того что вы предложили...

ALTER TABLE `jos_md_catalog_message_criterions` ADD INDEX
`ggg` USING BTREE ( ON ( `id_criterions_name`, `id_criterions_values` ) include(message_id) )

в чем ошибка тут?Предложил не я :),
include() - это вообще из MS SQL, в MySQL такого нет.
Покрывающий индекс (для этого запроса) в MySQL - (`id_criterions_name`, `id_criterions_values`, `message_id`)

...

Рейтинг:

0 / 0

17.01.2014, 16:19:26

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529886

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

Маленькая ремарка:
если в запросе есть поля, отсутствующие в индексе, то для получения их значения применяется операция KeyLookup - выборка недостающих данных из таблицы по первичному ключу.

если индекс покрывающий (т.е. все поля, задействованные в запросе, содержатся в индексе), то и чтения из таблицы не требуется.

первичный ключ содержится в любом индексе и дублировать его смысла нет

Пример из MS SQL:

...

Рейтинг:

0 / 0

17.01.2014, 16:36:10

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529906

folder-pro

Гость

Cygapb-007,

перепробовал кучу комбинаций и перестановок...

варианты которые юзает мускуль
message_id, id_criterions_name, id_criterions_values

даже удалил PK на всякий случай (чтобы не грешили на него в операциях группировки и т.п.)

...

Рейтинг:

0 / 0

17.01.2014, 16:46:22

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38529921

folder-pro

Гость

Дамп таблицы

http://clip2net.com/s/6CcIHY

...

Рейтинг:

0 / 0

17.01.2014, 16:55:13

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38530109

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

folder-proДамп таблицы

http://clip2net.com/s/6CcIHY

Если интересно, вот на этих данных результаты из MS SQL :)

Код: sql

-- create table sel(id int identity primary key, id_name int, id_val int);
-- insert sel (id_name,id_val)values(2,41),(3,46),(4,62),(5,65);

-- set statistics io,time on
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index(0))
	join sel s on s.id_name=r.id_criterions_name and s.id_val=r.id_criterions_values
	GROUP BY r.message_id
	HAVING count(distinct s.id) = (select COUNT(*) from sel)
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_MessNameCrit)
	join sel s on s.id_name=r.id_criterions_name and s.id_val=r.id_criterions_values
	GROUP BY r.message_id
	HAVING count(distinct s.id) = (select COUNT(*) from sel)
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_NameCrit)
	join sel s on s.id_name=r.id_criterions_name and s.id_val=r.id_criterions_values
	GROUP BY r.message_id
	HAVING count(distinct s.id) = (select COUNT(*) from sel)
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_NameCritMess)
	join sel s on s.id_name=r.id_criterions_name and s.id_val=r.id_criterions_values
	GROUP BY r.message_id
	HAVING count(distinct s.id) = (select COUNT(*) from sel)

SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index(0))
	where 
		r.id_criterions_name=2 and r.id_criterions_values=41 or
		r.id_criterions_name=3 and r.id_criterions_values=46 or
		r.id_criterions_name=4 and r.id_criterions_values=62 or
		r.id_criterions_name=5 and r.id_criterions_values=65
	GROUP BY r.message_id
	HAVING count(distinct r.id_criterions_name*100+r.id_criterions_values) = 4
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_MessNameCrit)
	where 
		r.id_criterions_name=2 and r.id_criterions_values=41 or
		r.id_criterions_name=3 and r.id_criterions_values=46 or
		r.id_criterions_name=4 and r.id_criterions_values=62 or
		r.id_criterions_name=5 and r.id_criterions_values=65
	GROUP BY r.message_id
	HAVING count(distinct r.id_criterions_name*100+r.id_criterions_values) = 4
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_NameCrit)
	where 
		r.id_criterions_name=2 and r.id_criterions_values=41 or
		r.id_criterions_name=3 and r.id_criterions_values=46 or
		r.id_criterions_name=4 and r.id_criterions_values=62 or
		r.id_criterions_name=5 and r.id_criterions_values=65
	GROUP BY r.message_id
	HAVING count(distinct r.id_criterions_name*100+r.id_criterions_values) = 4
SELECT r.message_id
	FROM jos_md_catalog_message_criterions r with(index=IX_NameCritMess)
	where 
		r.id_criterions_name=2 and r.id_criterions_values=41 or
		r.id_criterions_name=3 and r.id_criterions_values=46 or
		r.id_criterions_name=4 and r.id_criterions_values=62 or
		r.id_criterions_name=5 and r.id_criterions_values=65
	GROUP BY r.message_id
	HAVING count(distinct r.id_criterions_name*100+r.id_criterions_values) = 4

-- set statistics io,time off

http://www.sqlsentry.net/

...

Рейтинг:

0 / 0

17.01.2014, 19:41:49

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38530110

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

Эээ... картинки не прицепились... Одну все же вставлю, иначе смысла не было публиковать:)

...

Рейтинг:

0 / 0

17.01.2014, 19:43:53

| Ответить | Цитировать | Написать

Модель EAV (сущность - характеристики)

#38530141

Cygapb-007

Участник

Сообщения: 1 684

Рейтинг: 0 / 0

и план выполнения кривой выложил... Извиняюсь...
Вот корректный (со всеми вариантами запросов)

...

Рейтинг:

0 / 0

17.01.2014, 20:13:20

| Ответить | Цитировать | Написать

53 сообщений из 53, показаны все 3 страниц

все

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Модель EAV (сущность - характеристики)

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?all=1&fid=47&tid=1835382]:	0ms
get settings:	7ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	25ms
get topic data:	8ms
get forum data:	2ms
get page messages:	85ms
get tp. blocked users:	1ms
others:	200ms

total:	347ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы