Simple MySQL WHERE query not using Index

Question

Quick Answer: Query condition needs to be same type as column to use index. I was trying to search a CHAR column with a numeric condition.

I have a table with 15 million rows. I have a column called 'ticket' that can occur multiple times but it wouldn't occur very many times . . . probably less than 10. I have created an index on this column but the explain command says that when I am using a simple 'WHERE ticket =' query that it is not using the index. Obviously this confuses me as I am asking a question here.

CREATE TABLE company1.rTable (
  `aDate` date DEFAULT NULL,
  `fromD` date DEFAULT NULL,
  `pNo` int(10) DEFAULT NULL,
  `ticket` char(11) DEFAULT NULL,
  `r` int(11) unsigned NOT NULL DEFAULT '0',
  `line` tinyint(3) unsigned DEFAULT NULL,
  `nNum` char(11) NOT NULL,
  `pKey` char(7) DEFAULT NULL,
  `modNum` char(4) DEFAULT NULL,
  `dnum` smallint(5) unsigned NOT NULL,
  `rdNum` smallint(5) unsigned DEFAULT NULL,
  `pType` int(10) DEFAULT NULL,
  `lNum` decimal(9,2) DEFAULT NULL,
  `lineAmount` decimal(9,2) DEFAULT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `amount1` decimal(9,2) DEFAULT NULL,
  `amount2` decimal(9,2) DEFAULT NULL,
  `amount3` decimal(9,2) DEFAULT NULL,
  `amount4` decimal(9,2) DEFAULT NULL,
  `amount5` decimal(9,2) DEFAULT NULL,
  `amount6` decimal(9,2) DEFAULT NULL,
  `amount7` decimal(9,2) DEFAULT NULL,
  `amount8` decimal(9,2) DEFAULT NULL,
  `amount9` decimal(9,2) DEFAULT NULL,
  `amount10` decimal(9,2) DEFAULT NULL,
  `tType` tinyint(4) DEFAULT NULL,
  `lineB` decimal(9,2) DEFAULT NULL,
  `lineD` char(1) DEFAULT NULL,
  `lineP` decimal(9,2) DEFAULT NULL,
  `lineI` decimal(9,2) DEFAULT NULL,
  `lineC` decimal(9,2) DEFAULT NULL,
  `lineW` decimal(9,2) DEFAULT NULL,
  `lineR` decimal(9,2) DEFAULT NULL,
  `lineM` decimal(9,2) DEFAULT NULL,
  KEY `rADate` (`aDate`),
  KEY `rTType` (`tType`),
  KEY `rTicket` (`ticket`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

My query:

SELECT *
FROM   company1.rTable
WHERE  ticket = 3478421;

Maybe there is a way to change the query to use the index? I tried things like 'AND ticket > . . .' as a guess but that didn't help.

sometimes mysql will decide it's easier to just do a table scan. — Marc B
– Marc B, Commented May 14, 2013 at 19:06

Lazarus Lazaridis · Accepted Answer · 2013-05-14 19:07:49Z

2

ticket is a char column and you are using number condition (3478421).

answered May 14, 2013 at 19:07

Lazarus Lazaridis

6,0392 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donL Over a year ago

Thank you . . . I neither noticed or thought of that as mattering. I will pay attention to that in the future.

Collectives™ on Stack Overflow

Simple MySQL WHERE query not using Index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related