MySQL Full table scan

Question

Ok,on the following query DMSNODE has 144K rows. and there is an index on CHANGEDON, ID, PARENT ID and 2 permutations of the 3

SELECT
    DISTINCT t0.ID AS a1,
    t0.CHANGEDON,
    t1.id AS a2
FROM
    DMSNODE t0
    LEFT OUTER JOIN DMSNODE t1 ON (t1.ID = t0.PARENT_ID)
ORDER BY
    t0.CHANGEDON ASC
LIMIT
     0, 5

This is the table definition

CREATE TABLE `dmsnode` (
    `ID` BIGINT(20) NOT NULL,    
    `PARENT_ID` BIGINT(20) DEFAULT NULL,
    `TITLE` VARCHAR(1024) NOT NULL,
    `CHANGEDON` DATETIME NOT NULL,

    PRIMARY KEY (`ID`),
    KEY `DMSNODE_PARENT_ID_FK` (`PARENT_ID`),
    KEY `DMSNODE_CHANGEDON_IDX` (`CHANGEDON`),
    KEY `idx_dmsnode_ID_PARENT_ID_CHANGEDON` (`ID` , `PARENT_ID` , `CHANGEDON`),
    KEY `idx_dmsnode_ID_PARENT_ID` (`ID` , `PARENT_ID`),

    CONSTRAINT `DMSNODE_FOLDER_ID_FK` FOREIGN KEY (`PARENT_ID`)
        REFERENCES `dmsnode` (`ID`),
    )  ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT='DMS Node'

And EXPLAIN shows a full table scan

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      t0      index   ""idx_dmsnode_ID_PARENT_ID_CHANGEDON    25  ""  144982  Using index; Using temporary; Using filesort
1   SIMPLE      t1      eq_ref  PRIMARY,idx_dmsnode_ID_PARENT_ID_CHANGEDON,idx_dmsnode_ID_PARENT_ID PRIMARY 8   tbibms.t0.PARENT_ID 1   Using index

Can someone explain to why a full table scan is performed and how to avoid it? I thought that with the index on CHANGEDON would be enough to first select the top 5 changedon rows and then do the left outer join

It's probably the DISTINCT. Is it really needed or can you remove it or move it into a Derived Table? — dnoeth

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2015-02-14T11:05:08

There's no WHERE clause in your query so the database engine has to look at every row in both of these tables. In such a case a full table scan will result in less I/O than using an index.

Share and enjoy.

MySQL Full table scan

1 Answers