# odd indexing issue

**URL:** <https://forums.percona.com/t/odd-indexing-issue/1595>\
**Category:** Other MySQL® Questions\
**Created:** [January 19, 2011, 2:12pm UTC](https://forums.percona.com/t/odd-indexing-issue/1595 "2011-01-19T14:12:09Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [January 19, 2011, 2:12pm UTC](https://forums.percona.com/t/odd-indexing-issue/1595/1 "2011-01-19T14:12:09Z")

</div>

We have a large table, 13mm+ records. Specifically its a phplist table, structure is below.

Running this query:

select sum(clicked) from phplist\_linktrack where messageid = 113;

causes a full table scan to happen as is evident by running the query through explain.

What’s odd is that the same query using a different messageid does use the messageid index correctly.

My only “guess” is that the optimizer is seeing many records for messageid 113 and is opting to do a full scan rather than use the index.

Even using “use index” doesn’t force the issue.

mysql\> show create table phplist\_linktrack \G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Table: phplist\_linktrack  
Create Table: CREATE TABLE `phplist_linktrack` (  
`linkid` int(11) NOT NULL AUTO\_INCREMENT,  
`messageid` int(11) NOT NULL,  
`userid` int(11) NOT NULL,  
`url` varchar(255) DEFAULT NULL,  
`forward` text,  
`firstclick` datetime DEFAULT NULL,  
`latestclick` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
`clicked` int(11) DEFAULT ‘0’,  
PRIMARY KEY (`linkid`),  
UNIQUE KEY `messageid` (`messageid`,`userid`,`url`),  
KEY `midindex` (`messageid`),  
KEY `uidindex` (`userid`),  
KEY `urlindex` (`url`),  
KEY `miduidindex` (`messageid`,`userid`),  
KEY `miduidurlindex` (`messageid`,`userid`,`url`)  
) ENGINE=MyISAM AUTO\_INCREMENT=13206016 DEFAULT CHARSET=latin1

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [January 20, 2011, 4:43am UTC](https://forums.percona.com/t/odd-indexing-issue/1595/2 "2011-01-20T04:43:27Z")

</div>

iberkner wrote on Wed, 19 January 2011 20:42

> [@](#):
>
> My only “guess” is that the optimizer is seeing many records for messageid 113 and is opting to do a full scan rather than use the index.
> 
> Even using “use index” doesn’t force the issue.

That sounds like the correct conclusion, the use index is just a recommendation to the optimizer so it is still free to choose to perform a table scan if it thinks your recommendation isn’t any good.

To speed up this query you should create a index on (message\_id, clicked):

– Create new index covering message\_id and clicked = only a range scan of the index instead of table scanALTER TABLE phplist\_linktrack ADD INDEX phplistlink\_ix\_messageid\_clicked(message\_id, clicked);

And while you are at it you could drop these indexes that are redundant due to that you have other composite indexes that begins with these columns.

– Dropping redundant indexesALTER TABLE phplist\_linktrack DROP INDEX midindex; – Drop this since it is redundantALTER TABLE phplist\_linktrack DROP INDEX miduidindex; – Drop this since it is redundant

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [January 20, 2011, 5:20am UTC](https://forums.percona.com/t/odd-indexing-issue/1595/3 "2011-01-20T05:20:43Z")

</div>

miduidurlindex also seems quite redundant to me.

---

<div class="post-metadata">

**Author:** ![gsxauto](https://avatars.discourse-cdn.com/v4/letter/g/ac91a4/32.png) [@gsxauto](https://forums.percona.com/u/gsxauto)\
**Post date:** [January 31, 2011, 6:33pm UTC](https://forums.percona.com/t/odd-indexing-issue/1595/4 "2011-01-31T18:33:14Z")

</div>

Try using FORCE INDEX.