MYSQL Not using Possible Keys

Question

I have 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 join with 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 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 table "element" using key attribute_material_id for indexing. But if I left join with 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 query like this:

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;

The 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 can see the table "element" or not using any possible keys.

Whats wrong with the query or table structure in the 2nd query ?

Thanks in advance for taking a look at my case.

Any tips or critics appreciated!

Answer 1

Nothing is wrong with the table structure, the indexes could potentially be improved. The main problem with the indexes are that all of them index a single field only. MySQL can use only 1 index per table in a query.

Reasons why MySQL could have decided against using any index:

1. The element table is small. 5123 records are nothing for an rdbms. MySQL can decide against using an index if the table is small because it may be less efficient to open the index and search based on that and then go to the table and fetch the matching data, rather than simply searching through all records in the table.

2. is_finalised field probably has 2 possible values (or 3 max), so its selectivity is low, therefore MySQL is unlikely to use it anyway.

3. Both queries are against the sql standard by grouping by on a single field and including several fields in the select list that are neither in the group by clause, nor are subject to aggregation, nor are functionally dependent on the group by fields. Fortunately, the more recent versions of mysql block such queries by default.

What I would do to make mysql more likely to use an index:

1. Change the left join into an inner join (you are filtering on the right hand side table turning the left join into an inner one effectively).

2. Add multi-column index to elements table covering attribute_material_id, elements_id fields.

Obviously, you should consider rewriting your query to comply with the sql standard.