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);
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()*5 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 |
SET profiling = 1;
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| MAX(name) |
|---|
| 37ee9640-c211-11ef-b3c7-d720191c40ee |
select MAX(name) FROM categories WHERE type=1;
| MAX(name) |
|---|
| 37ee9640-c211-11ef-b3c7-d720191c40ee |
SHOW PROFILES;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.04764225 | select MAX(name) FROM categories ignore index(type) WHERE type=1 |
| 2 | 0.16639675 | 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 | categories | null | ALL | null | null | null | null | 100001 | 0.00 | Using where |
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 | categories | null | ref | type | type | 4 | const | 50000 | 100.00 | null |
explain analyze
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| EXPLAIN |
|---|
| -> Aggregate: max(categories.`name`) (cost=10000 rows=1) (actual time=59.1..59.1 rows=1 loops=1) -> Filter: (categories.`type` = 1) (cost=10000 rows=1) (actual time=0.0541..38.3 rows=100001 loops=1) -> Table scan on categories (cost=10000 rows=100001) (actual time=0.0524..30.8 rows=100001 loops=1) |
explain analyze
select MAX(name) FROM categories WHERE type=1;
| EXPLAIN |
|---|
| -> Aggregate: max(categories.`name`) (cost=10001 rows=1) (actual time=142..142 rows=1 loops=1) -> Index lookup on categories using type (type=1) (cost=5001 rows=50000) (actual time=0.136..122 rows=100001 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 |
-- SELECT * -- SUM(Duration) AS TotalDuration
-- FROM information_schema.profiling
-- WHERE Query_ID > 1;