# mysql not using index

**URL:** <https://forums.percona.com/t/mysql-not-using-index/187>\
**Category:** Other MySQL® Questions\
**Created:** [February 1, 2007, 6:12am UTC](https://forums.percona.com/t/mysql-not-using-index/187 "2007-02-01T06:12:23Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![cillu](https://avatars.discourse-cdn.com/v4/letter/c/8797f3/32.png) [@cillu](https://forums.percona.com/u/cillu)\
**Post date:** [February 1, 2007, 6:12am UTC](https://forums.percona.com/t/mysql-not-using-index/187/1 "2007-02-01T06:12:23Z")

</div>

Hi, I have these 2 tables;

CREATE TABLE `transaction_it_200701` (  
`transid` varchar(100) NOT NULL default ‘’,  
`service` varchar(100) NOT NULL default ‘’,  
…  
 PRIMARY KEY (`transid`),  
KEY `data` (`start_date`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `transactionlog_it_200701` (  
`code` bigint(20) NOT NULL auto\_increment,  
`type` varchar(100) NOT NULL default ‘’,  
`transid` varchar(100) NOT NULL default ‘0’,  
`transLogid` varchar(100) NOT NULL default ‘0’,  
`status` int(11) NOT NULL default ‘0’,  
…  
PRIMARY KEY (`code`),  
KEY `transidentifier` (`transid`,`transLogid`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Both the tables contains about 3 million of rows.

I have this query:  
explain SELECT \* FROM transaction\_it\_200701 transaction INNER JOIN transactionlog\_it\_200701 transactionlog ON transaction.transid = t  
ransactionlog.transid WHERE transaction.start\_date \>= ‘2007-01-22 00:41:00’ and transaction.start\_date \<= ‘2007-01-26 00:41:59’ GROUP BY 1,3;

and the EXPLAIN result is:

±—±------------±---------------±-------±------------- —±--------±--------±----------------------------------- ±--------±--------------------------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±---------------±-------±------------- —±--------±--------±----------------------------------- ±--------±--------------------------------+  
| 1 | SIMPLE | transactionlog | ALL | transidentifier | NULL | NULL | NULL | 2489139 | Using temporary; Using filesort |  
| 1 | SIMPLE | transaction | eq\_ref | PRIMARY,data | PRIMARY | 300 | reporting\_1.transactionlog.transid | 1 | Using where |  
±—±------------±---------------±-------±------------- —±--------±--------±----------------------------------- ±--------±--------------------------------+  
2 rows in set (0.00 sec)

It seems the second table index in not used.

In production this query takes 10 minutes!! ☹

Could you help me?

Thanks.  
Cillu.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [February 1, 2007, 1:55pm UTC](https://forums.percona.com/t/mysql-not-using-index/187/2 "2007-02-01T13:55:01Z")

</div>

Index is used on transaction table but not transactionlog…

You did not list which type start\_date has. Also check what kind of selectivity does this range have - might be it is large and so full table scan is more efficient.

Also note you use SELECT \* together with GROUP BY 1,3 which is extremely fragile.