Bug #60457 Select is not using index

• Submitted: 14 Mar 2011 9:00
• Modified: 15 Mar 2011 9:10
• Reporter: Satyapal Garhwal
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.5.9
• OS: Any
• CPU Architecture: Any
• Tags: Select is not using index

[14 Mar 2011 9:00] Satyapal Garhwal

Description:
explain SELECT 1
   FROM core_variant_combination_details
 WHERE variant_combination_number = 15214;

this is not using index 

The ddl of table is 

DROP TABLE IF EXISTS `optimus`.`core_variant_combination_details`;
CREATE TABLE  `optimus`.`core_variant_combination_details` (
  `variant_combination_details_id` int(30) NOT NULL AUTO_INCREMENT,
  `variant_combination_number` varchar(25) NOT NULL,
  `variant_type_code` varchar(25) NOT NULL,
  `sequence_number` int(4) DEFAULT NULL,
  `variant_id` int(4) DEFAULT NULL,
  `variant_value` varchar(30) DEFAULT NULL,
  `design_id` int(10) DEFAULT NULL,
  `design_number` varchar(25) DEFAULT NULL,
  `variant_number` varchar(25) DEFAULT NULL,
  `item_code` varchar(25) DEFAULT NULL,
  `created_by` varchar(15) NOT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`variant_combination_details_id`),
  KEY `FK_core_variant_combination_details_1` (`variant_combination_number`),
  KEY `FK_core_variant_combination_details_2` (`variant_type_code`),
  KEY `NUK_core_variant_combination_details_variant_id` (`variant_id`) USING BTREE,
  KEY `NUK_core_variant_combination_details_design_id_no_variant_no` (`design_id`,`design_number`,`variant_number`) USING BTREE,
  KEY `NUK_core_variant_combination_details_item_code` (`item_code`) USING BTREE,
  KEY `IK_variant_details` (`variant_combination_number`) USING BTREE,
  CONSTRAINT `FK_core_variant_combination_details_1` FOREIGN KEY (`variant_combination_number`) REFERENCES `core_variant_combination_header` (`variant_combination_number`)
) ENGINE=InnoDB AUTO_INCREMENT=273609 DEFAULT CHARSET=utf8;

How to repeat:
explain SELECT 1
   FROM core_variant_combination_details
 WHERE variant_combination_number = 15214;

[14 Mar 2011 9:12] Valeriy Kravchuk

Please, send the output of:

SELECT count(*)
   FROM core_variant_combination_details
 WHERE variant_combination_number = 15214;

SHOW TABLE STATUS LIKE 'core_variant_combination_details'\G

SELECT count(distinct variant_combination_number) FROM core_variant_combination_details;

from your environment.

Send also the results of EXPLAIN for your problematic query.

[15 Mar 2011 5:06] Satyapal Garhwal

sorry i did a mistake 
i am using  WHERE variant_combination_number = 15214;
integer  15214 in varchar column ....
so that it is not using index 

if i use WHERE variant_combination_number = '15214'; it is using index

[15 Mar 2011 9:10] Valeriy Kravchuk

Then this is not a bug. Read the manual, http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html:

"For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'."