Search code examples
mariadb

Why does mariadb's ANALYZE say "USING INDEX" when my search has a leading wildcard?

I have a mariadb table with an index on username. When I ANALYZE a query against USERNAME using LIKE "%name%", it claims that it is "using index". But why? How can you use an index with a leading wildcard?

MariaDB [ejbca]> ANALYZE SELECT fingerprint FROM CertificateData WHERE username LIKE "%thename%";
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+
| id   | select_type | table           | type  | possible_keys | key                  | key_len | ref  | rows    | r_rows     | filtered | r_filtered | Extra                    |
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+
|    1 | SIMPLE      | CertificateData | index | NULL          | certificatedata_idx2 | 1003    | NULL | 1421198 | 1539326.00 |   100.00 |       0.00 | Using where; Using index |
+------+-------------+-----------------+-------+---------------+----------------------+---------+------+---------+------------+----------+------------+--------------------------+

This is an AWS mariadb instance, with version 10.6.16-MariaDB-log.

The Certificates table looks like:

CREATE TABLE `CertificateData` (
  `fingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `base64Cert` longtext DEFAULT NULL,
  `cAFingerprint` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `certificateProfileId` int(11) NOT NULL,
  `endEntityProfileId` int(11) DEFAULT NULL,
  `crlPartitionIndex` int(11) DEFAULT NULL,
  `expireDate` bigint(20) NOT NULL,
  `issuerDN` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `notBefore` bigint(20) DEFAULT NULL,
  `revocationDate` bigint(20) NOT NULL,
  `revocationReason` int(11) NOT NULL,
  `rowProtection` longtext DEFAULT NULL,
  `rowVersion` int(11) NOT NULL,
  `serialNumber` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `status` int(11) NOT NULL,
  `subjectAltName` varchar(2000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `subjectDN` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `subjectKeyId` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `accountBindingId` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `tag` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `type` int(11) NOT NULL,
  `updateTime` bigint(20) NOT NULL,
  `username` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `certificateRequest` longtext DEFAULT NULL,
  `invalidityDate` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`fingerprint`),
  UNIQUE KEY `certificatedata_idx12` (`serialNumber`,`issuerDN`),
  KEY `certificatedata_idx2` (`username`),
  KEY `certificatedata_idx4` (`subjectDN`),
  KEY `certificatedata_idx5` (`type`),
  KEY `certificatedata_idx6` (`issuerDN`,`status`),
  KEY `certificatedata_idx7` (`certificateProfileId`),
  KEY `certificatedata_idx11` (`subjectKeyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
Solution

  • It means it is reading through all of the index instead of all of the main body of table to find matching rows. If the index is smaller than the main table data, this can be faster, especially if the index contains both the filtering column(s) and selected column(s).

    https://mariadb.com/kb/en/explain/:

    Using index

    Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.