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,
`type` varchar(5) 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()*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 |
SET profiling = 1;
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| MAX(name) |
|---|
| 1bbd1fae-c2ee-11ef-9c49-f990e3c42b8b |
select MAX(name) FROM categories WHERE type=1;
| MAX(name) |
|---|
| 1bbd1fae-c2ee-11ef-9c49-f990e3c42b8b |
SHOW PROFILES;
| Query_ID | Duration | Query |
|---|---|---|
| 1 | 0.05105750 | select MAX(name) FROM categories ignore index(type) WHERE type=1 |
| 2 | 0.05147025 | 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 | 10.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 | ALL | type | null | null | null | 100001 | 10.00 | Using where |
explain analyze
select MAX(name) FROM categories ignore index(type) WHERE type=1;
| EXPLAIN |
|---|
| -> Aggregate: max(categories.`name`) (cost=11000 rows=1) (actual time=60.7..60.7 rows=1 loops=1) -> Filter: (categories.`type` = 1) (cost=10000 rows=10000) (actual time=0.049..40.2 rows=100001 loops=1) -> Table scan on categories (cost=10000 rows=100001) (actual time=0.0461..28.9 rows=100001 loops=1) |
explain analyze
select MAX(name) FROM categories WHERE type=1;
| EXPLAIN |
|---|
| -> Aggregate: max(categories.`name`) (cost=11000 rows=1) (actual time=61..61 rows=1 loops=1) -> Filter: (categories.`type` = 1) (cost=10000 rows=10000) (actual time=0.0497..40.3 rows=100001 loops=1) -> Table scan on categories (cost=10000 rows=100001) (actual time=0.0463..28.9 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;