How to optimize join query?

Question

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

`... SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), ... GROUP BY account_id ...` is non-deterministic and produces indefinite output. See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9bb62bed2596e0f2f1c7c90ee796d412 - data is the same but outputs differs. — Akina, Dec 23 '21 at 12:51
@Akina My purpose is to get the recent account_status of each account from table spend_daily_level with SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), "," , 1) AS account_status. Is there any better way? — jia Jimmy, Dec 24 '21 at 09:31
DISTINCT and ORDER BY in GROUP_CONCAT contradicts. DISTINCT is preferred and acts as hidden inner GROUP BY, in this case ORDER BY acts over indefinite column values obtained in incomplete grouping. You must remove DISTINCT and use `ORDER BY` only. — Akina, Dec 24 '21 at 09:37

score 2 · Accepted Answer · answered Dec 24 '21 at 06:06

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,

My purpose is to get the recent `account_status` of each `account` from table `spend_daily_level` with `SUBSTRING_INDEX( GROUP_CONCAT(DISTINCT account_status ORDER BY date DESC), "," , 1) AS account_status`. Is there any better way? — jia Jimmy, Dec 24 '21 at 09:28
The optimal groupwise-max solutions are [_here_](http://mysql.rjweb.org/doc.php/groupwise_max) — Rick James, Dec 24 '21 at 16:54

How to optimize join query?

1 Answers1