Děkuji za odpověď.
Jde o široký sortiment produktů. Každá skupina produktů má svoje vlastnosti, podle kterých je následovně filtrováno.
Abych nemusel použít extra tabulku pro každou skupinu produktů mám jednu tabulku "product", která obsahuje parametry společné pro všechny produkty (výška, šířka, barva atd.).
Pokud skupina produktů vyžaduje specifické parametry jsou zaneseny do EAV tabulky. Zpracování dotazu nabere zpoždění v momentě, kdy potřebuju všechny EAV parametry spojit.
product_feature_group
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name_cz | varchar(45) | YES | | NULL | |
| name_en | varchar(45) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
product_feature_group_item
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name_cz | varchar(45) | YES | | NULL | |
| name_en | varchar(45) | YES | | NULL | |
| feature_group_id | int(10) unsigned | NO | | NULL | |
+------------------+------------------+------+-----+---------+----------------+
product_feature_group_item_value;
+-----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| feature_group_id | int(10) unsigned | NO | | NULL | |
| value_cz | varchar(100) | YES | | NULL | |
| value_en | varchar(100) | YES | | NULL | |
| feature_group_item_id | int(10) unsigned | NO | | NULL | |
+-----------------------+------------------+------+-----+---------+----------------+
product_has_feature
+--------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| feature_group_id | int(10) unsigned | NO | | NULL | |
| feature_group_item_id | int(10) unsigned | NO | | NULL | |
| feature_group_item_value_id| int(10) unsigned | NO | | NULL | |
+--------------------------+------------------+------+-----+---------+----------------+
product_features_group (Zvuk) -> product_feature_group_item (Výkon, Počet pásem, Impedance) -> product_filter_group_item_value (V12,V15,V20,PP2,PP3,I4,I8)
Skupina parametrů = product_feature_group
Podmnožina parametrů = product_feature_group_item
Hodnoty parametrů = product_feature_group_item_value
Asociování parametrů k produktům = product_has_feature
show indexes from product_has_feature;
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_has_feature | 0 | PRIMARY | 1 | id | A | 812822 | NULL | NULL | | BTREE | | |
| product_has_feature | 1 | product_has_feature_product_id | 1 | product_id | A | 22578 | NULL | NULL | | BTREE | | |
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)
show indexes from product_feature_group
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_feature_group | 0 | PRIMARY | 1 | id | A | 13 | NULL | NULL | | BTREE | | |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Všechny tabulky pak spojuji následujícím postupem:
SELECT product.id,product_feature_group.name_cz from product
join product_has_feature on product_has_feature.product_id = product.id
join product_feature_group on product_feature_group.id = product_has_feature.feature_group_id
zabere 3,3 vteřiny/klient
Uvědomuju si že EAV není v souladu s SQL, ale potřebuji vyhovět požadavku použití SQL.