Mysql index not working

Question

Have the following table structure:

CREATE TABLE `PARSER_U_R_L` (
  `PARSER_ID` varchar(20) COLLATE latin1_general_cs NOT NULL,
  `URL_MD5` varchar(255) COLLATE latin1_general_cs NOT NULL,
  `ENTRY_POINT_ID` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
  `TYPE_ID` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
  `STATUS_ID` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
  `INDEXED_TIME` datetime DEFAULT NULL,
PRIMARY KEY (`PARSER_ID`,`URL_MD5`),
  KEY `PURL_PARSER` (`PARSER_ID`))
 ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

As you see PRIMARY KEY in the Parser_id and URL_MD5. When I try a simple select

EXPLAIN 
    SELECT   * 
    FROM  `PARSER_U_R_L` 
    WHERE `URL_MD5` IN ( ids )

In EXPLAIN of this select I have possible keys = Null.

What can be problem ?

schtever · Accepted Answer · 2015-08-28 13:43:31Z

1

The problem is that the URL_MD5 is not the first column in your primary key. Since you have an index with multiple columns, the optimizer won't use that index unless you supply a value for the first column as well.

If you supply a value for just the first column, the optimizer will use it, so try reversing the columns in the index.

answered Aug 28, 2015 at 13:43

schtever

3,25019 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql index not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related