•  

Announcement

Announcement Module
Collapse
No announcement yet.

odd indexing issue

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • odd indexing issue

    #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
    Tags: None

  • #2
    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

    Comment


    • #3
      miduidurlindex also seems quite redundant to me.

      Comment


      • #4
        Try using FORCE INDEX.

        Comment

        Working...
        X