I have a leading wildcard query I know it is hard to optimize.

select id from customer where seid = 134 and telephone like '%18749618910%'

CREATE TABLE `customer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `seid` bigint(20) unsigned NOT NULL DEFAULT '0' ,
  `ccgeid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `cid` bigint(20) unsigned NOT NULL DEFAULT '0' /* foreign key */
  `telephone` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL
  `create_time` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`seid`),
  KEY `s_telephone` (`seid`,`telephone`)
  UNIQUE KEY `seid_ccgeid_phone` (`seid`,`ccgeid`,`telephone`),
  KEY `s_cid` (`seid`,`cid`),
)

Resorting to FTS is the long term goal. But since I only need id here I did some experiment to see if I improve some query performance. But I was surprised to see select * ... is faster than select id .... I have around 2.5 millions records, select * alway takes about 1 second while select id takes about 4 to 5 seconds.

The explain Extra information shows select id Using where while select * Using index condition, both with same possible_keys.

But as Bill Karwin explained in What does MySQl explain Extra “Using where” really mean?, Using where does not help, while I can’t find a clear explanation for Using index condition (mysql document does not help).

So why select * is much faster than select id in my case ?

PS. the explain details:

explain select id from customer where seid = 134 and telephone like '%18749618910%'

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer_telephones
   partitions: p6
         type: ref
possible_keys: seid_ccgeid_phone,s_cid,s_telephone
          key: s_cid
      key_len: 8
          ref: const
         rows: 1278146
     filtered: 11.11
        Extra: Using where


explain select * from customer_telephones where seid = 134 and telephone like '%18749618910%'
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer_telephones
   partitions: p6
         type: ref
possible_keys: seid_ccgeid_phone,s_cid,s_telephone
          key: seid_ccgeid_phone
      key_len: 8
          ref: const
         rows: 1278146
     filtered: 11.11
        Extra: Using index condition