Building MySQL InnoDB clustered indexes

Question

Assuming that you have a table like

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

What I'm unsure about is why

SELECT COUNT(1) FROM t1;

Takes 19 minutes (I've populated the table with 1.2 million rows).

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

I wonder if the problem here is the cardinality. My question is should InnoDB indexes be ordered by the higher cardinality columns closer to the the start of the index, i.e. PRIMARY KEY (provider_id,search_id); or does it even matter?

Answer 1

So as @RickJames, suggested in one of the comments, cardinality has little to do with order of indexes. Therefore if your query is similar to

SELECT id, search_id, provider_id FROM t1 WHERE search_id=123 AND provider_id=567

Then the index should be ordered search_id,provider_id. If however you are running a more range based query.

For efficiency, the "=constant" column should come before the "range" column in the INDEX

That means whenever you have a query as follows.

SELECT id, search_id, provider_id FROM t1 WHERE search_id>123 AND
provider_id<567 AND columnX=1

Then your index should be created as follows columnX,search_id,provider_id.