odd indexing issue

MySQL & MariaDB Other MySQL® Questions
1

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

2

iberkner wrote on Wed, 19 January 2011 20:42

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

3

miduidurlindex also seems quite redundant to me.

4

Try using FORCE INDEX.