How to speed up the slow query MYSQL

Question

Here's my db tables:

#1  User

Create Table: CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) DEFAULT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(254) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  `user_name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8


#  settlement
Create Table: CREATE TABLE `settlement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `short_title` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  UNIQUE KEY `short_title` (`short_title`)
) ENGINE=InnoDB AUTO_INCREMENT=3553 DEFAULT CHARSET=utf8



# sign
Create Table: CREATE TABLE `sign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `our_side_entity` smallint(6) NOT NULL,
  `contact` varchar(63) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `emails` varchar(1023) NOT NULL,
  `settlement_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sign_our_side_entity_settlement_id_6ccd6800_uniq` (`our_side_entity`,`settlement_id`),
  KEY `sign_settlement_id_07f0b7bb_fk_settlement_id` (`settlement_id`),
  CONSTRAINT `sign_ibfk_1` FOREIGN KEY (`settlement_id`) REFERENCES `settlement` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3684 DEFAULT CHARSET=utf8


# sale
Create Table: CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `is_leader` tinyint(1) NOT NULL,
  `default_leader_id` int(11) NOT NULL,
  `sale_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `default_leader_id` (`default_leader_id`),
  KEY `sale_id` (`sale_id`),
  CONSTRAINT `sale_ibfk_1` FOREIGN KEY (`default_leader_id`) REFERENCES `User` (`id`),
  CONSTRAINT `sale_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8


# account_token
Create Table: CREATE TABLE `account_token` (
  `token_id` varchar(32) NOT NULL,
  `token_type` smallint(6) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8


#   account
Create Table: CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `account_id` varchar(45) NOT NULL,
  `entity` varchar(255) NOT NULL,
  `is_pc` tinyint(1) DEFAULT NULL,
  `type` smallint(6) NOT NULL,
  `settlement_type` smallint(6) NOT NULL,
  `medium` smallint(6) NOT NULL,
  `ae_id` int(11) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `sign_id` int(11) NOT NULL,
  `sale_manage_id` int(11) DEFAULT NULL,
  `is_new_customer` tinyint(1) DEFAULT '0',
  `agency` tinyint(1) DEFAULT '0',
  `related_entity` varchar(255) DEFAULT NULL,
  `token_id` varchar(45) DEFAULT NULL,
  `account_token_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_account_id_medium_b46819c8_uniq` (`account_id`) USING BTREE,
  KEY `account_ae_id_4d0b721c_fk_auth_user_id` (`ae_id`),
  KEY `account_sale_id_7770cbad_fk_auth_user_id` (`sale_id`),
  KEY `account_sign_id_17d08191_fk_sign_id` (`sign_id`),
  KEY `sale_manage_id` (`sale_manage_id`),
  KEY `account_token` (`account_token_id`),
  CONSTRAINT `account_ibfk_1` FOREIGN KEY (`ae_id`) REFERENCES `User` (`id`),
  CONSTRAINT `account_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `User` (`id`),
  CONSTRAINT `account_ibfk_3` FOREIGN KEY (`sign_id`) REFERENCES `sign` (`id`),
  CONSTRAINT `account_ibfk_4` FOREIGN KEY (`sale_manage_id`) REFERENCES `sale` (`id`),
  CONSTRAINT `account_ibfk_5` FOREIGN KEY (`account_token_id`) REFERENCES `account_token` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=119532 DEFAULT CHARSET=utf8

# sale_leader
Create Table: CREATE TABLE `sale_leader` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `year` smallint(6) NOT NULL,
  `month` smallint(6) NOT NULL,
  `leader_id` int(11) DEFAULT NULL,
  `sale_manage_id` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `leader_id` (`leader_id`),
  KEY `sale_manage_id` (`sale_manage_id`),
  CONSTRAINT `sale_leader_ibfk_1` FOREIGN KEY (`leader_id`) REFERENCES `User` (`id`),
  CONSTRAINT `sale_leader_ibfk_2` FOREIGN KEY (`sale_manage_id`) REFERENCES `sale` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=985 DEFAULT CHARSET=utf8

#   Table: spend_daily_level
Create Table: CREATE TABLE `spend_daily_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `date` date NOT NULL,
  `system_value` decimal(16,2) NOT NULL,
  `checked_value` decimal(16,2) NOT NULL,
  `account_id` int(11) NOT NULL,
  `sale_leader_id` int(11) DEFAULT NULL,
  `account_status` tinyint(3) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `spend_daily_level_date_account_id_f38b1186_uniq` (`date`,`account_id`),
  KEY `spend_daily_level_account_id_f6df4f99_fk_account_id` (`account_id`),
  KEY `sale_leader_id` (`sale_leader_id`),
  KEY `date_active` (`active`,`date`),
  CONSTRAINT `spend_daily_level_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`),
  CONSTRAINT `spend_daily_level_ibfk_2` FOREIGN KEY (`sale_leader_id`) REFERENCES `sale_leader` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3682983 DEFAULT CHARSET=utf8

The final query made by ORM is：

SELECT `User`.`user_name`, `account`.`medium`, `account`.`type`, `settlement`.`title`, 
`spend_daily_level`.`system_value`, T7.`user_name`, `spend_daily_level`.`date` 
FROM `spend_daily_level` INNER JOIN `account` ON (`spend_daily_level`.`account_id` = `account`.`id`) 
LEFT OUTER JOIN `User` ON (`account`.`sale_id` = `User`.`id`) 
INNER JOIN `sign` ON (`account`.`sign_id` = `sign`.`id`) 
INNER JOIN `settlement` ON (`sign`.`settlement_id` = `settlement`.`id`) 
LEFT OUTER JOIN `sale_leader` ON (`spend_daily_level`.`sale_leader_id` = `sale_leader`.`id`) 
LEFT OUTER JOIN `User` T7 ON (`sale_leader`.`leader_id` = T7.`id`) 
WHERE (`spend_daily_level`.`date` >= '2020-05-13' AND `spend_daily_level`.`date` <= '2020-10-28');

Explain SQL ABOVE:

+----+-------------+-------------------+--------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+
| id | select_type | table             | type   | possible_keys                                                                                       | key                                                 | key_len | ref                                          | rows | Extra       |
+----+-------------+-------------------+--------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+
|  1 | SIMPLE      | settlement        | index  | PRIMARY                                                                                             | title                                               | 767     | NULL                                         | 3563 | Using index |
|  1 | SIMPLE      | sign              | ref    | PRIMARY,sign_settlement_id_07f0b7bb_fk_settlement_id                                                | sign_settlement_id_07f0b7bb_fk_settlement_id        | 4       | project.settlement.id                    |    1 | Using index |
|  1 | SIMPLE      | account           | ref    | PRIMARY,account_sign_id_17d08191_fk_sign_id                                                         | account_sign_id_17d08191_fk_sign_id                 | 4       | project.sign.id                          |   28 | NULL        |
|  1 | SIMPLE      | User              | eq_ref | PRIMARY                                                                                             | PRIMARY                                             | 4       | project.account.sale_id                  |    1 | NULL        |
|  1 | SIMPLE      | spend_daily_level | ref    | spend_daily_level_date_account_id_f38b1186_uniq,spend_daily_level_account_id_f6df4f99_fk_account_id | spend_daily_level_account_id_f6df4f99_fk_account_id | 4       | project.account.id                       |   13 | Using where |
|  1 | SIMPLE      | sale_leader       | eq_ref | PRIMARY                                                                                             | PRIMARY                                             | 4       | project.spend_daily_level.sale_leader_id |    1 | NULL        |
|  1 | SIMPLE      | T7                | eq_ref | PRIMARY                                                                                             | PRIMARY                                             | 4       | project.sale_leader.leader_id            |    1 | NULL        |
+----+-------------+-------------------+--------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+

And it cost me about 8s with total 145k rows data. How can I speed up this query?

Great Thanks.

Updated with adding index suggested

#  User  count(110)
Create Table: CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) DEFAULT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(254) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  `user_name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE,
  KEY `user_idx` (`id`,`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=136 DEFAULT CHARSET=utf8


# settlement count(4151)
Create Table: CREATE TABLE `settlement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `short_title` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  UNIQUE KEY `short_title` (`short_title`),
  KEY `settle_idx` (`id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=4162 DEFAULT CHARSET=utf8



# sign  count(4306)
Create Table: CREATE TABLE `sign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `our_side_entity` smallint(6) NOT NULL,
  `contact` varchar(63) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `emails` varchar(1023) NOT NULL,
  `settlement_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sign_our_side_entity_settlement_id_6ccd6800_uniq` (`our_side_entity`,`settlement_id`),
  KEY `sign_settlement_id_07f0b7bb_fk_settlement_id` (`settlement_id`),
  KEY `sign_idx` (`id`,`settlement_id`),
  CONSTRAINT `sign_ibfk_1` FOREIGN KEY (`settlement_id`) REFERENCES `settlement` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4314 DEFAULT CHARSET=utf8

# sale (46)
Create Table: CREATE TABLE `sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `is_leader` tinyint(1) NOT NULL,
  `default_leader_id` int(11) NOT NULL,
  `sale_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `default_leader_id` (`default_leader_id`),
  KEY `sale_id` (`sale_id`),
  CONSTRAINT `sale_ibfk_1` FOREIGN KEY (`default_leader_id`) REFERENCES `User` (`id`),
  CONSTRAINT `sale_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8


# account_token (125)
Create Table: CREATE TABLE `account_token` (
  `token_id` varchar(32) NOT NULL,
  `token_type` smallint(6) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8


# account (134663)
Create Table: CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `account_id` varchar(45) NOT NULL,
  `entity` varchar(255) NOT NULL,
  `is_pc` tinyint(1) DEFAULT NULL,
  `type` smallint(6) NOT NULL,
  `settlement_type` smallint(6) NOT NULL,
  `medium` smallint(6) NOT NULL,
  `ae_id` int(11) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `sign_id` int(11) NOT NULL,
  `sale_manage_id` int(11) DEFAULT NULL,
  `is_new_customer` tinyint(1) DEFAULT '0',
  `agency` tinyint(1) DEFAULT '0',
  `related_entity` varchar(255) DEFAULT NULL,
  `account_token_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_account_id_medium_b46819c8_uniq` (`account_id`) USING BTREE,
  KEY `account_ae_id_4d0b721c_fk_auth_user_id` (`ae_id`),
  KEY `account_sale_id_7770cbad_fk_auth_user_id` (`sale_id`),
  KEY `account_sign_id_17d08191_fk_sign_id` (`sign_id`),
  KEY `sale_manage_id` (`sale_manage_id`),
  KEY `account_token_id` (`account_token_id`),
  KEY `account_idx` (`id`,`sale_id`,`sign_id`,`medium`,`type`),
  CONSTRAINT `account_ibfk_1` FOREIGN KEY (`ae_id`) REFERENCES `User` (`id`),
  CONSTRAINT `account_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `User` (`id`),
  CONSTRAINT `account_ibfk_3` FOREIGN KEY (`sign_id`) REFERENCES `sign` (`id`),
  CONSTRAINT `account_ibfk_4` FOREIGN KEY (`sale_manage_id`) REFERENCES `sale` (`id`),
  CONSTRAINT `account_ibfk_5` FOREIGN KEY (`account_token_id`) REFERENCES `account_token` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=134910 DEFAULT CHARSET=utf8


# sale_leader (1003)
Create Table: CREATE TABLE `sale_leader` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `year` smallint(6) NOT NULL,
  `month` smallint(6) NOT NULL,
  `leader_id` int(11) DEFAULT NULL,
  `sale_manage_id` int(11) NOT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `leader_id` (`leader_id`),
  KEY `sale_manage_id` (`sale_manage_id`),
  CONSTRAINT `sale_leader_ibfk_1` FOREIGN KEY (`leader_id`) REFERENCES `User` (`id`),
  CONSTRAINT `sale_leader_ibfk_2` FOREIGN KEY (`sale_manage_id`) REFERENCES `sale` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1007 DEFAULT CHARSET=utf8

# spend_daily_level (2,177,793)
Create Table: CREATE TABLE `spend_daily_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) NOT NULL,
  `created_time` datetime(6) NOT NULL,
  `updated_time` datetime(6) NOT NULL,
  `date` date NOT NULL,
  `system_value` decimal(16,2) NOT NULL,
  `checked_value` decimal(16,2) NOT NULL,
  `account_id` int(11) NOT NULL,
  `sale_leader_id` int(11) DEFAULT NULL,
  `account_status` tinyint(3) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `spend_daily_level_date_account_id_f38b1186_uniq` (`date`,`account_id`),
  KEY `spend_daily_level_account_id_f6df4f99_fk_account_id` (`account_id`),
  KEY `sale_leader_id` (`sale_leader_id`),
  KEY `date_active` (`active`,`date`),
  KEY `spend_idx` (`date`,`account_id`,`system_value`),
  CONSTRAINT `spend_daily_level_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`),
  CONSTRAINT `spend_daily_level_ibfk_2` FOREIGN KEY (`sale_leader_id`) REFERENCES `sale_leader` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4181988 DEFAULT CHARSET=utf8



EXPLAIN SELECT `User`.`user_name`, `account`.`medium`, `account`.`type`, `settlement`.`title`, `spend_daily_level`.`system_value`, T7.`user_name`, `spend_daily_level`.`date` FROM `spend_daily_level` INNER JOIN `account` ON (`spend_daily_level`.`account_id` = `account`.`id`) LEFT OUTER JOIN `User` ON (`account`.`sale_id` = `User`.`id`) INNER JOIN `sign` ON (`account`.`sign_id` = `sign`.`id`) INNER JOIN `settlement` ON (`sign`.`settlement_id` = `settlement`.`id`) LEFT OUTER JOIN `sale_leader` ON (`spend_daily_level`.`sale_leader_id` = `sale_leader`.`id`) LEFT OUTER JOIN `User` T7 ON (`sale_leader`.`leader_id` = T7.`id`) WHERE (`spend_daily_level`.`date` >= '2020-05-13' AND `spend_daily_level`.`date` <= '2020-10-28');
+----+-------------+-------------------+--------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+
| id | select_type | table             | type   | possible_keys                                                                                                 | key                                                 | key_len | ref                                          | rows | Extra       |
+----+-------------+-------------------+--------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+
|  1 | SIMPLE      | settlement        | index  | PRIMARY,settle_idx                                                                                            | title                                               | 767     | NULL                                         | 4214 | Using index |
|  1 | SIMPLE      | sign              | ref    | PRIMARY,sign_settlement_id_07f0b7bb_fk_settlement_id,sign_idx                                                 | sign_settlement_id_07f0b7bb_fk_settlement_id        | 4       | bv_crm_1029.settlement.id                    |    1 | Using index |
|  1 | SIMPLE      | account           | ref    | PRIMARY,account_sign_id_17d08191_fk_sign_id,account_idx                                                       | account_sign_id_17d08191_fk_sign_id                 | 4       | bv_crm_1029.sign.id                          |   20 | NULL        |
|  1 | SIMPLE      | User              | eq_ref | PRIMARY,user_idx                                                                                              | PRIMARY                                             | 4       | bv_crm_1029.account.sale_id                  |    1 | NULL        |
|  1 | SIMPLE      | spend_daily_level | ref    | spend_daily_level_date_account_id_f38b1186_uniq,spend_daily_level_account_id_f6df4f99_fk_account_id,spend_idx | spend_daily_level_account_id_f6df4f99_fk_account_id | 4       | bv_crm_1029.account.id                       |   20 | Using where |
|  1 | SIMPLE      | sale_leader       | eq_ref | PRIMARY                                                                                                       | PRIMARY                                             | 4       | bv_crm_1029.spend_daily_level.sale_leader_id |    1 | NULL        |
|  1 | SIMPLE      | T7                | eq_ref | PRIMARY,user_idx                                                                                              | PRIMARY                                             | 4       | bv_crm_1029.sale_leader.leader_id            |    1 | NULL        |
+----+-------------+-------------------+--------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+---------+----------------------------------------------+------+-------------+

It seems that the index added not used.

Answer 1

You may try adding the following indices:

CREATE INDEX spend_idx ON spend_daily_level (date, account_id, system_value);
CREATE INDEX account_idx ON account (id, sale_id, sign_id, medium, type);
CREATE INDEX user_idx ON User (id, user_name);
CREATE INDEX sign_idx ON sign (id, settlement_id);
CREATE INDEX settle_idx ON settlement (id, title);

These first index spend_idx covers the entire WHERE clause. It, along with the other indices, cover the joins and the select clause.