title: MYSQL Not using Possible Keys
body: <p>I have main table called "element":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Then I left join with this table called "products_material":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>With query like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>EXPLAIN result:</p>
  
  <pre><code>+----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
  | 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 |
  +----+-------------+-------+------------+-------+--------------------------------------------+-----------------------+---------+-------+------+----------+------------------------------------+
  </code></pre>
  
  <p>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":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>with query like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The EXPLAIN result:</p>
  
  <pre><code>+----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
  | 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) |
  +----+-------------+-------+------------+------+--------------------------------------------+---------+---------+-------+------+----------+----------------------------------------------------+
  </code></pre>
  
  <p>As you can see the table "element" or not using any possible keys.</p>
  
  <p>Whats wrong with the query or table structure in the 2nd query ?</p>
  
  <p>Thanks in advance for taking a look at my case.</p>
  
  <p>Any tips or critics appreciated!</p>