1

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.

  • Prefix columns cannot be skipped. Additional condition by prefix column may help. But if it is 2-valued column, it makes no sense to include it into the index at all, especially when the percent of each separate value is over 5-7%. – Akina Jul 18 at 12:44
  • @Akina thanks for your advice, if my column value be 7-8 (tinyint) do i will get any performance issues by indexing this column? so should i remove postType from index? and will create index only with postLocation and postArea? – Rhidoy Jul 18 at 13:01
  • It must be tested. In most cases the field which have a short values list (less then 10 values, especially when the amount for each separate value is approximately the same) may be not included into index. But you must look in complex - the field combination summarized values list is more wide. And when all fields are used in condition expression it is too hard to predict does the field excluding will make the execution more effective or not. Practice only will answer... – Akina Jul 18 at 13:22
0

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.)

  • that table was a demo table so there was no primary key. and yeah i did some test and EXPLAIN showing that it work same if use both (0,1) or (1). when it's (0,1) explain lookup more rows, when it's just (1) explain look up few rows, because all the data within 0,1 but few data in 1. so i think optimizer don't skip index type if i include both type (0,1) in query, it just fetch all the rows which belong type 0 or 1. That's the problem why i don't understand MySQL properly, because when i include both two type MySQL should skip type index, just will got next index. – Rhidoy Jul 20 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.