I have a main table called "element":
CREATE TABLE `element` (
`elements_id` int(11) NOT NULL AUTO_INCREMENT,
`elements_code` varchar(32) DEFAULT NULL,
`elements_name` varchar(128) DEFAULT NULL,
`elements_description` text,
`elements_image` varchar(64) DEFAULT NULL,
`attribute_category_id` int(11) DEFAULT '0',
`attribute_material_id` int(11) DEFAULT '0',
`attribute_color_id` int(11) DEFAULT '0',
`attribute_shape_id` int(11) DEFAULT '0',
`attribute_surface_id` int(11) DEFAULT '0',
`attribute_size_id` int(11) DEFAULT '0',
`attribute_holesize_id` int(11) DEFAULT '0',
`attribute_cut_id` int(11) DEFAULT '0',
`attribute_height_id` int(11) NOT NULL DEFAULT '0',
`attribute_width_id` int(11) NOT NULL DEFAULT '0',
`attribute_thickness_id` int(11) NOT NULL DEFAULT '0',
`attribute_clasp_id` int(11) NOT NULL DEFAULT '0',
`attribute_setting_id` int(11) NOT NULL DEFAULT '0',
`attribute_chain_id` int(11) NOT NULL DEFAULT '0',
`elements_weight` decimal(5,3) DEFAULT NULL,
`elements_weight_goldpure` decimal(5,3) NOT NULL DEFAULT '0.000',
`elements_supplier` varchar(64) DEFAULT NULL,
`elements_price` decimal(10,5) DEFAULT NULL,
`add_date` datetime DEFAULT NULL,
`add_by` varchar(30) DEFAULT NULL,
`is_finalized` char(1) DEFAULT '0',
`stars` tinyint(4) NOT NULL DEFAULT '0',
`wax_complexity` char(1) DEFAULT NULL,
`elements_dioh_target` varchar(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`elements_id`),
KEY `attribute_category_id` (`attribute_category_id`),
KEY `attribute_material_id` (`attribute_material_id`),
KEY `attribute_color_id` (`attribute_color_id`),
KEY `attribute_shape_id` (`attribute_shape_id`),
KEY `attribute_surface_id` (`attribute_surface_id`),
KEY `attribute_size_id` (`attribute_size_id`),
KEY `attribute_holesize_id` (`attribute_holesize_id`),
KEY `attribute_cut_id` (`attribute_cut_id`),
KEY `attribute_height_id` (`attribute_height_id`),
KEY `attribute_width_id` (`attribute_width_id`),
KEY `attribute_thickness_id` (`attribute_thickness_id`),
KEY `is_finalized` (`is_finalized`)
) ENGINE=MyISAM AUTO_INCREMENT=12687 DEFAULT CHARSET=latin1
Then I left a connection to this table called "products_material":
CREATE TABLE `products_materials` (
`products_materials_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`material_name` varchar(128) NOT NULL DEFAULT '',
`active_status` char(1) DEFAULT '0',
`sort_number` int(4) DEFAULT '0',
`label_active_status` char(1) DEFAULT '0',
PRIMARY KEY (`products_materials_id`)
) ENGINE=MyISAM AUTO_INCREMENT=120 DEFAULT CHARSET=latin1
With a query like this:
SELECT e.*, pm.material_name AS mat_name
FROM element e
LEFT JOIN products_materials pm ON pm.products_materials_id=e.attribute_material_id
WHERE e.is_finalized='1' AND 1 = 1 AND pm.products_materials_id = '1' GROUP BY e.elements_id HAVING 1 = 1 ORDER BY e.elements_id;
EXPLAIN result:
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | pm | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using filesort |
| 1 | SIMPLE | e | NULL | ref | PRIMARY,attribute_material_id,is_finalized | attribute_material_id | 5 | const | 10 | 98.20 | Using index condition; Using where |
+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
As you can see the "element" table, using the attribute_material_id key for indexing. But if I left a connection to this table called "elements_attributes_description":
CREATE TABLE `elements_attributes_description` (
`elements_attributes_decription_id` int(11) NOT NULL AUTO_INCREMENT,
`elements_attributes_id` int(11) DEFAULT NULL,
`languages_id` int(11) DEFAULT NULL,
`elements_attributes_groups` int(11) DEFAULT NULL,
`name` varchar(64) NOT NULL DEFAULT '',
`description` text NOT NULL,
PRIMARY KEY (`elements_attributes_decription_id`),
UNIQUE KEY `Unique` (`elements_attributes_id`,`languages_id`,`elements_attributes_groups`),
KEY `index3` (`elements_attributes_groups`),
KEY `Index 1` (`elements_attributes_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1776 DEFAULT CHARSET=latin1
with such a request:
SELECT e.*, ead2.name AS mat_name
FROM element e
LEFT JOIN elements_attributes_description ead2 ON ead2.elements_attributes_id = e.attribute_material_id AND ead2.elements_attributes_groups = 2
WHERE e.is_finalized='1' AND ead2.elements_attributes_id = '1' GROUP BY e.elements_id HAVING 1 = 1 ORDER BY e.elements_id;
EXPLAIN result:
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | ead2 | NULL | ref | Unique,index3,Index 1 | Index 1 | 5 | const | 30 | 19.08 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | e | NULL | ALL | PRIMARY,attribute_material_id,is_finalized | NULL | NULL | NULL | 5123 | 70.20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
As you see the "element" table or not using any possible keys.
What is wrong with the query or table structure in the second query?
Thanks in advance for considering my case.
Any advice or critics appreciated!