Mysql – SQL indexing with 2 join tables

I have 3 tables. country, team, player

table Country

id name
1   x
2   x
3   x

table Team

id  name country_id
1    x      1
2    x      1
3    x      1

table Player

id  name country_id team_id
1    x      1         1
2    x      1         1
3    x      1         1

There are a few data in country and team but 8 million data in player.

it takes more than 30 seconds to get this data

select 
    c.id as country_id, 
    c.name as country_name, 
    t.id as team_id, 
    t.name as team_name, 
    p.id as player_id, 
    p.name as player_name, 
    p.created_at 
from 
    player p 
join country c 
    on c.id = p.country_id 
join team t 
    on t.id = p.team_id 
where 
    c.id = 1
order by 
    p.created_at DESC 
limit 10, 10;

In player there are 4 indexes

+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| player |          0 | PRIMARY                     |            1 | id          | A         |      557608 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| player |          1 | FKb21w76q5ho5gx5270qg5docnt |            1 | country_id  | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| player |          1 | FKdvd6ljes11r44igawmpm1mc5s |            1 | team_id     | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| player |          1 | idx_player_created          |            1 | created_at  | D         |      195396 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

I tried to get Explain and EXPLAIN ANALYZE here is what I get

EXPLAIN

+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys               | key                         | key_len | ref             | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range | PRIMARY                     | PRIMARY                     | 8       | NULL            |    2 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | NULL       | ref   | FKb21w76q5ho5gx5270qg5docnt | FKb21w76q5ho5gx5270qg5docnt | 8       | query_test.c.id | 2972 |   100.00 | NULL                                         |
+----+-------------+-------+------------+-------+-----------------------------+-----------------------------+---------+-----------------+------+----------+----------------------------------------------+

EXPLAIN ANALYZE

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 1/10 row(s)  (actual time=34787..34787 rows=1 loops=1)
    -> Sort: p.id DESC, limit input to 11 row(s) per chunk  (actual time=34787..34787 rows=11 loops=1)
        -> Stream results  (cost=7929 rows=5944) (actual time=0.442..34548 rows=1.13e+6 loops=1)
            -> Nested loop inner join  (cost=7929 rows=5944) (actual time=0.433..33974 rows=1.13e+6 loops=1)
                -> Nested loop inner join  (cost=5848 rows=5944) (actual time=0.426..33200 rows=1.13e+6 loops=1)
                    -> Filter: (c.id in (1,3))  (cost=0.91 rows=2) (actual time=0.0292..0.0476 rows=2 loops=1)
                        -> Index range scan on c using PRIMARY over (id = 1) OR (id = 3)  (cost=0.91 rows=2) (actual time=0.0273..0.0439 rows=2 loops=1)
                    -> Index lookup on p using FKb21w76q5ho5gx5270qg5docnt (country_id=c.id)  (cost=2775 rows=2972) (actual time=0.282..16570 rows=564564 loops=2)
                -> Single-row index lookup on t using PRIMARY (id=p.team_id)  (cost=0.25 rows=1) (actual time=520e-6..544e-6 rows=1 loops=1.13e+6)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

without ORDER BY it just takes 0.01 sec

Is it possible to fetch the query result in a short period of time with offset query?

I’m aware of cursor pagination but I would like to know better query with offset pagination.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

table information

player

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| player | CREATE TABLE `player` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `country_id` bigint NOT NULL,
  `team_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FKdvd6ljes11r44igawmpm1mc5s` (`team_id`),
  KEY `idx_player_created` (`created_at` DESC),
  KEY `FKb21w76q5ho5gx5270qg5docnt` (`country_id`),
  CONSTRAINT `FKb21w76q5ho5gx5270qg5docnt` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`),
  CONSTRAINT `FKdvd6ljes11r44igawmpm1mc5s` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8088248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

country

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table
                                                                                  |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| country | CREATE TABLE `country` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

team

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| team  | CREATE TABLE `team` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `country_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FKqv6wvrq3qclb3gvo92gg2y6q7` (`country_id`),
  CONSTRAINT `FKqv6wvrq3qclb3gvo92gg2y6q7` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+