How to skip prefix column check in multi column index in MySQL? 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.
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 


fetch rows with (0,1,15)
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.)