By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `categories` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`type` int unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=1100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- create index ix_name on categories(name);
create index ix_type_name on categories(type,name);
Records: 0 Duplicates: 0 Warnings: 0
INSERT INTO categories (name, type) VALUES (uuid() , 1);
set @@cte_max_recursion_depth=1000001;
INSERT INTO categories (name, type)
with recursive r as(
select 1 idx, uuid() name
,1 type
union all
select idx+1 ,uuid() name
-- , 1 type -- version 1
, cast(rand()*10000 as unsigned) type -- version 2
from r
where idx<100000
)
select name,type from r;
Records: 100000 Duplicates: 0 Warnings: 0
SHOW VARIABLES LIKE 'innodb_stats_on_metadata';
| Variable_name | Value |
|---|---|
| innodb_stats_on_metadata | OFF |
SET GLOBAL innodb_stats_on_metadata=ON;
SHOW INDEXES FROM categories;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| categories | 0 | PRIMARY | 1 | id | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | type | 1 | type | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | ix_type_name | 1 | type | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | ix_type_name | 2 | name | A | 1 | null | null | BTREE | YES | null |
SET profiling = 1;
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| MAX(name) |
|---|
| 4c6604a4-c214-11ef-9ec8-c47b1c1a31a7 |
select MAX(name) FROM categories WHERE type=1;
| MAX(name) |
|---|
| 4c6604a4-c214-11ef-9ec8-c47b1c1a31a7 |
SHOW PROFILES;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.00073550 | select MAX(name) FROM categories ignore index(type) WHERE type=1 |
| 2 | 0.00020950 | select MAX(name) FROM categories WHERE type=1 |
explain
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | null | null | null | null | null | null | null | null | null | Select tables optimized away |
explain
select MAX(name) FROM categories WHERE type=1;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | null | null | null | null | null | null | null | null | null | Select tables optimized away |
explain analyze
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| EXPLAIN |
|---|
| -> Rows fetched before execution (cost=0..0 rows=1) (actual time=165e-6..230e-6 rows=1 loops=1) |
explain analyze
select MAX(name) FROM categories WHERE type=1;
| EXPLAIN |
|---|
| -> Rows fetched before execution (cost=0..0 rows=1) (actual time=126e-6..196e-6 rows=1 loops=1) |
SHOW INDEXES FROM categories;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| categories | 0 | PRIMARY | 1 | id | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | type | 1 | type | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | ix_type_name | 1 | type | A | 1 | null | null | BTREE | YES | null | |||
| categories | 1 | ix_type_name | 2 | name | A | 1 | null | null | BTREE | YES | null |
-- SELECT * -- SUM(Duration) AS TotalDuration
-- FROM information_schema.profiling
-- WHERE Query_ID > 1;