mysql is using key (primary) not listed in possible

my table is like:

CREATE TABLE `payments`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deleted` tinyint(2) NOT NULL,
  `actionType` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `payDate` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `isCoupayOrder` tinyint(2) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index`(`payDate`, `actionType`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6543773 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

when i execute this query command,

explain select * from payments where actionType in ('APPROVED','CANCEL') and deleted <> 1 and isCoupayOrder = 0 and (payDate between '2020-05-01' and '2020-11-01') order by id asc limit 20;

the result is this:

id 1
select_type SIMPLE
table payments
type index 
possible_keys index
key PRIMARY 
key_len 8
extra Using where

i don't understand why the key is primary, means scan the whole table? it's not even listed in possible_keys.

CodePudding user response：

The optimizer chooses not to use the index index for the where query. This could be caused by the fact the optimizer thinks it is faster to do a table scan as you select all the columns (select *), some of them not found in the index.

When it's doing the table scan, it can use the PRIMARY index to satisfy the ORDER BY-clause. Hence the use of it.