Mariadb - select count() using index but select * not using proper index

Question

Version - 10.4.25-MariaDB

I have a table where column(name) is a second part of primary key(idarchive,name). When i run count(*) on table where name like 'done%', its using the index on field name properly but when i run select * its not using the separate index instead using primary key and slowing down the query.

Any idea what we can do here ? any changes in optimizer switch or any other alternative which can help ? Note - we can't use force index as queries are not controlable.

Table structure:

CREATE TABLE `table1 ` (
  `idarchive` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `idsite` int(10) unsigned DEFAULT NULL,
  `date1` date DEFAULT NULL,
  `date2` date DEFAULT NULL,
  `period` tinyint(3) unsigned DEFAULT NULL,
  `ts_archived` datetime DEFAULT NULL,
  `value` double DEFAULT NULL,
  PRIMARY KEY (`idarchive`,`name`),
  KEY `index_idsite_dates_period` (`idsite`,`date1`,`date2`,`period`,`ts_archived`),
  KEY `index_period_archived` (`period`,`ts_archived`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Queries:

explain select count(*) from table1 WHERE name  like 'done%' ;
+------+-------------+-------------------------------+-------+---------------+------+---------+------+---------+--------------------------+
| id   | select_type | table                         | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+------+-------------+-------------------------------+-------+---------------+------+---------+------+---------+--------------------------+
|    1 | SIMPLE      | table1                        | range | name          | name | 767     | NULL | 9131455 | Using where; Using index |
+------+-------------+-------------------------------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.000 sec)

explain select * from table1 WHERE name  like 'done%' ;
+------+-------------+-------------------------------+------+---------------+------+---------+------+----------+-------------+
| id   | select_type | table                         | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+------+-------------+-------------------------------+------+---------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      | table1                        | ALL  | name          | NULL | NULL    | NULL | 18262910 | Using where |
+------+-------------+-------------------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.000 sec) ```

Answer 1

Your SELECT COUNT(*) ... LIKE 'constant%' query is covered by your index on your name column. That is, the entire query can be satisfied by reading the index. So the query planner decides to range-scan your index to generate the result.

On the other hand, your SELECT * query needs all columns from all rows of the table. That can't be satisfied from any of your indexes. And, it's possible your WHERE name like 'done%' filter reads a significant fraction of the table, enough so the query planner decides the fastest way to satisfy it is to scan the entire table. The query planner figures this out by using statistics on the contents of the table, plus some knowledge of the relative costs of IO and CPU.

If you have just inserted many rows into the table you could try doing ANALYZE TABLE table1 and then rerun the query. Maybe after the table's statistics are updated you'll get a different query plan.

And, if you don't need all the columns, you could stop using SELECT * and instead name the columns you need. SELECT * is a notorious query-performance antipattern, because it often returns column data that never gets used. Once you know exactly what columns you want, you could create a covering index to provide them.

These days the query planner does a pretty good job of optimizing simple queries such as yours.

In MariaDB you can say ANALYZE FORMAT=JSON SELECT.... It will run the query and show you details of the actual execution plan it used.