How to skip prefix column check in multi column index in MySQL?

Posted on

Question :

Below is my demo table structure:

CREATE TABLE `table1` (
  `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent',
  `postLocation` smallint(6) NOT NULL,
  `postArea` smallint(6) DEFAULT NULL,
  `postDetails` text,
  KEY `table1_postType_postLocation_postArea_index` (`postType`,`postLocation`,`postArea`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here i have index on column (postType, postLocation, postArea). My postType column value are fixed, it will be 0 or 1.

How can i use index (postType, postLocation, postArea) or skip postType column value and use other two index?

If i include 0, 1 in query is it gonna skip the postType column from index check? or will it check index first for postType column in 1 or 2 than will continue to next column in the index? Below is an example:

select *
from table1 t
where t.postType in (0, 1)
  and t.postLocation = 1
  and t.postArea = 15;

Please suggest if i need to update my datatype for postType column.

Answer :

It depends. Since you are using MySQL 8.0, the index starting with type is probably fine. I am guessing that the optimizer will be smart enough to

  1. fetch rows with (0,1,15)
  2. Continue fetching, now for rows with (1,1,15).

You can get some clues from EXPLAIN or EXPLAIN FORMAT=JSON.

You can ‘prove’ that it is (or is not) doing it in that optimal way by this technique:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

If the total of the Handler_read_% values is very close to the number of rows returned, then it probably did it that optimal way. Else the Handler_read values may be much larger.

I see no PRIMARY KEY on that table. If that triple is ‘unique’ then make it the PK. This will speed it up a little more due to “clustering” of the PK.

If the index is not good enough, rearrange it to have type last.

(Pet peeve: Drop the constant “post” from column names; it clutters queries.)

Leave a Reply

Your email address will not be published.