The sql I finally use is like:
SELECT
t1.account_id,
t1.system_value,
t1.date,
acs.account_status
FROM
(
SELECT
account.id,
account.account_id,
system_value,
`date`
FROM
account,
spend_daily_level
WHERE
account.id = spend_daily_level.`account_id`
AND DATE BETWEEN '2021-12-01' AND '2021-12-23'
AND account.`account_id` in (
SELECT
`market_accounts_`.`id`
AS `account_id`
FROM
`market_accounts_`
WHERE
(`market_accounts_`.`ae` IN (112)
AND `market_accounts_`.`medium` IN (0, 1, 2, 3, 18)))) t1
LEFT JOIN
(
SELECT
account_id,
SUBSTRING_INDEX(
GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), "," , 1) AS account_status
FROM
spend_daily_level
GROUP BY
account_id ) acs
ON
t1.id = acs.account_id
And below are tables desc info:
# spend_daily_level
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 COMMENT,
`sale_leader_id` int(11) DEFAULT NULL ,
`account_status` tinyint(3) DEFAULT,
`growth` tinyint(1) NOT NULL DEFAULT,
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=24976133 DEFAULT CHARSET=utf8
# account
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,
`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,
`entity_id` int(11) DEFAULT NULL,
`merged_entity_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 `entity_id` (`entity_id`),
KEY `a_me_id` (`merged_entity_id`),
CONSTRAINT `a_me_id` FOREIGN KEY (`merged_entity_id`) REFERENCES `merged_entity` (`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`),
CONSTRAINT `account_ibfk_6` FOREIGN KEY (`entity_id`) REFERENCES `entity` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=303393 DEFAULT CHARSET=utf8
# market_accounts_
CREATE TABLE `market_accounts_` (
`agency` int(11) DEFAULT NULL,
`id` varchar(64) NOT NULL,
`entity` varchar(255) DEFAULT NULL,
`merged_entity` varchar(255) DEFAULT NULL,
`related_entity` varchar(255) DEFAULT NULL,
`type` tinyint(1) DEFAULT NULL,
`our_side_entity` varchar(255) DEFAULT NULL,
`short_title` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL COMMENT,
`ae` int(11) DEFAULT NULL,
`sale` int(11) DEFAULT NULL,
`medium` tinyint(2) DEFAULT NULL,
`is_pc` tinyint(1) DEFAULT NULL,
`is_new_customer` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# explain
id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra |
-- ----------- ----------------- ------ --------------------------------------------------------------------------------------------------- --------------------------------------------------- ------- ----------------------------------- -------- ---------------------
1|PRIMARY |<derived2> |ALL | | | | | 23356| |
1|PRIMARY |<derived4> |ref |<auto_key0> |<auto_key0> |4 |t1.id | 453| |
4|DERIVED |spend_daily_level|index |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 | |10599162| |
2|DERIVED |spend_daily_level|range |spend_daily_level_date_account_id_f38b1186_uniq,spend_daily_level_account_id_f6df4f99_fk_account_id|spend_daily_level_date_account_id_f38b1186_uniq |3 | | 23356|Using index condition|
2|DERIVED |account |eq_ref|PRIMARY |PRIMARY |4 |bv_crm.spend_daily_level.account_id| 1| |
2|DERIVED |market_accounts_ |eq_ref|PRIMARY |PRIMARY |258 |func | 1|Using where |
Now the sql executed very slowly, how can I speed it up?
Great thanks
CodePudding user response：
SUBSTRING_INDEX(...) looks like a very slow way to do "groupwise-max". Please find a better way. See the tag I added.
And you probably did not want the LEFT.
These indexes may help:
market_accounts_: INDEX(ae, medium, id)
spend_daily_level: INDEX(account_id, date) -- in this order
spend_daily_level: INDEX(account_id, account_status)
And drop this as redundant: spend_daily_level_account_id_f6df4f99_fk_account_id
Please use the modern JOIN syntax:
FROM account, spend_daily_level
WHERE account.id = spend_daily_level.`account_id`
AND ...
==>
FROM account
JOIN spend_daily_level ON account.id = spend_daily_level.`account_id`
WHERE ...
The inconsistency here could bite you someday:
account: `account_id` varchar(45) NOT NULL,
market_accounts_: `id` varchar(64) NOT NULL,