Home>

I am using a query that uses the columns "SetTime" and "SiteName" for where and the column "SetTime" for order by.
"Set Time" and "Site Name" are composite indexes, but when explained, they become "using index condition" instead of "using index".
I tried to specify only the key as the index of only "SetTime", but the result did not change.

Why isn't it a using index?
How can I make it a using index?

Query and explain

Query with a composite index specified by force index

select * from dhashdiff force index (analyze_join_index)
where SetTime<= 637410929158399725&&
SiteName ='Kojima Net'
order by SetTime
limit 1000;
+ ---- + ------------- + ----------- + ------------ + ----- --+ -------------------- + -------------------- + ----- ---- + ------- + --------- + ---------- + ----------------- ------ +
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ----------- + ------------ + ----- --+ -------------------- + -------------------- + ----- ---- + ------- + --------- + ---------- + ----------------- ------ +
1 | SIMPLE | dhashdiff | NULL | range | analyze_join_index | analyze_join_index | 266 | NULL | 1951470 | 10.00 | Using index condition |
+ ---- + ------------- + ----------- + ------------ + ----- --+ -------------------- + -------------------- + ----- ---- + ------- + --------- + ---------- + ----------------- ------ +


Query with compound index as force index and SetTime as force key

select * from dhashdiff force index (analyze_join_index) force key (settime_index)
where SetTime<= 637410929158399725&&
SiteName ='Kojima Net'
order by SetTime
limit 1000;
+ ---- + ------------- + ----------- + ------------ + ----- -+ ---------------------------------- + ------------ --- + --------- + ------ + --------- + ------------ + -------- --------------- +
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ----------- + ------------ + ----- -+ ---------------------------------- + ------------ --- + --------- + ------ + --------- + ------------ + -------- --------------- +
1 | SIMPLE | dhashdiff | NULL | range | settime_index, analyze_join_index | settime_index | 8 | NULL | 1951470 | 10.00 | Using index condition |
+ ---- + ------------- + ----------- + ------------ + ----- -+ ---------------------------------- + ------------ --- + --------- + ------ + --------- + ------------ + -------- --------------- +
create table
+ ----------- + -------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
| dhashdiff | CREATE TABLE `dhashdiff` (
  `SiteImgUrl` varbinary (2083) NOT NULL,
  `DHashDif` smallint (5) unsigned NOT NULL,
  `SetTime` bigint (19) unsigned NOT NULL,
  `ASIN` varbinary (10) NOT NULL,
  `SiteName` varchar (64) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`SiteImgUrl`,` ASIN`, `SiteName`),
  KEY `image_asin` (`ASIN`, `SiteImgUrl`),
  KEY `settime_index` (`SetTime`),
  KEY `asin` (`ASIN`),
  KEY `analyze_join_index` (`SetTime`, `SiteName`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci |
+ ----------- + -------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------- +
  • Answer # 1

    Why isn't it a using index?

    The reason why it is "using index condition" is that it contains an optimization called ICP (reference).

    How can I make it a using index?

    Because using index condition is more efficientIt doesn't make sense..

  • Answer # 2

    "When all the information of the column specified by SELECT and the column specified by WHERE is included in the index" is said to be using index. (Https://techlife.cookpad.com/entry/2017/04/18/092524)

    If you are requesting only the columns contained in the index key, such as select SetTime, SiteName from ~, you will get using index.
    Even if you create an index that contains all the columns you need, you'll still get a using index.

    In the example given, I guess there is no performance problem with using index condition.