server side count speed so slow. with left join

casuist

Link to test case:
Debugger code (debug.datatables.net):

[
  "Editor PHP libraries - version 2.1.2",
  {
    "query": "SELECT COUNT( `info_betting`.`ib_idx` ) as `cnt` FROM  `info_betting` LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`  LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`  LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`  LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`  LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`  LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id` WHERE `info_betting`.`ib_branch_id` = :where_0 AND  `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-10' AND '2023-10-25 23:59:59' ",
    "bindings": [
      {
        "name": ":where_0",
        "value": 18,
        "type": null
      }
    ]
  },
  {
    "query": "SELECT COUNT( `info_betting`.`ib_idx` ) as `cnt` FROM  `info_betting` LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`  LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`  LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`  LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`  LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`  LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id` WHERE `info_betting`.`ib_branch_id` = :where_0 AND  `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-10' AND '2023-10-25 23:59:59' ",
    "bindings": [
      {
        "name": ":where_0",
        "value": 18,
        "type": null
      }
    ]
  },
  {
    "query": "SELECT  `info_betting`.`ib_idx` as 'info_betting.ib_idx', `info_betting`.`ib_user_id` as 'info_betting.ib_user_id', `info_betting`.`ib_type` as 'info_betting.ib_type', `info_betting`.`ib_amount` as 'info_betting.ib_amount', `info_betting`.`ib_status` as 'info_betting.ib_status', `info_betting`.`ib_flag` as 'info_betting.ib_flag', `info_betting`.`ib_pay` as 'info_betting.ib_pay', `info_betting`.`ib_recom` as 'info_betting.ib_recom', `info_betting`.`ib_cnt` as 'info_betting.ib_cnt', `info_betting`.`ib_minigame` as 'info_betting.ib_minigame', `info_betting`.`ib_division` as 'info_betting.ib_division', `info_betting`.`ib_benefit` as 'info_betting.ib_benefit', `info_betting`.`ib_service_benefit` as 'info_betting.ib_service_benefit', `info_betting`.`ib_c_effective_bet_money` as 'info_betting.ib_c_effective_bet_money', `info_betting`.`ib_c_betting_date` as 'info_betting.ib_c_betting_date', `info_betting`.`ib_c_type` as 'info_betting.ib_c_type', `info_betting`.`ib_c_gid` as 'info_betting.ib_c_gid', `info_betting`.`ib_c_vendor` as 'info_betting.ib_c_vendor', `info_betting`.`ib_c_gubun` as 'info_betting.ib_c_gubun', `info_betting`.`ib_c_api_vendor` as 'info_betting.ib_c_api_vendor', `info_betting`.`ib_c_result_money` as 'info_betting.ib_c_result_money', `info_betting`.`ib_c_before_money` as 'info_betting.ib_c_before_money', `info_betting`.`ib_c_after_money` as 'info_betting.ib_c_after_money', `info_betting`.`ib_c_table_id` as 'info_betting.ib_c_table_id', `info_betting`.`ib_c_remark` as 'info_betting.ib_c_remark', `info_betting`.`ib_c_is_except` as 'info_betting.ib_c_is_except', `info_betting`.`ib_cut_money` as 'info_betting.ib_cut_money', `users`.`id` as 'users.id', `users`.`user_name` as 'users.user_name', `users`.`nick_name` as 'users.nick_name', `users`.`partner_id` as 'users.partner_id', `user_etc_infos`.`uei_lower_users_count` as 'user_etc_infos.uei_lower_users_count', `user_etc_infos`.`uei_lower_partners_count` as 'user_etc_infos.uei_lower_partners_count', `user_etc_infos`.`uei_parents` as 'user_etc_infos.uei_parents', `user_etc_infos`.`uei_parents_user_name` as 'user_etc_infos.uei_parents_user_name', `user_etc_infos`.`uei_parents_nick_name` as 'user_etc_infos.uei_parents_nick_name', `user_etc_infos`.`uei_user_depth` as 'user_etc_infos.uei_user_depth', `partners`.`id` as 'partners.id', `partners`.`user_name` as 'partners.user_name', `casino_games`.`name` as 'casino_games.name', `casino_games`.`name_ko` as 'casino_games.name_ko', `casino_games`.`image` as 'casino_games.image', `casino_games`.`icon` as 'casino_games.icon', `casino_games`.`game_code` as 'casino_games.game_code', `casino_games`.`sid` as 'casino_games.sid', `casino_tables`.`ct_name_ko` as 'casino_tables.ct_name_ko', `casino_providers`.`id` as 'casino_providers.id', `casino_providers`.`cp_name_ko` as 'casino_providers.cp_name_ko' FROM  `info_betting` LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`  LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`  LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`  LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`  LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`  LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id` WHERE `info_betting`.`ib_branch_id` = :where_0 AND  `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-15' AND '2023-10-25 23:59:59'  ORDER BY `info_betting`.`ib_idx`  desc  LIMIT 25",
    "bindings": [
      {
        "name": ":where_0",
        "value": 18,
        "type": null
      }
    ]
  }
]

Error messages shown:
Description of problem:
Server-side data table editor count rate is too slow.
Tested SQL calculations in debug information.

SELECT 
    COUNT(`info_betting`.`ib_idx`) AS `cnt`
FROM
    `info_betting`
    LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`
    LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`
    LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`
    LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`
    LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`
    LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id`
WHERE
    `info_betting`.`ib_branch_id` = 18
    AND `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-10' AND '2023-10-25';

explain

1   SIMPLE  info_betting    ref info_betting_ib_branch_id_IDX,info_betting_ib_c_betting_date_IDX,info_betting_ib_dbupgt_IDX,info_betting_bd_IDX,info_betting_ib_division_IDX    info_betting_bd_IDX 5   const   9510853 Using index condition
1   SIMPLE  users   eq_ref  id  id  4   isports.info_betting.ib_user_id 1   Using where

cnt 5,838,584
time 20.107 s

I tested this after removing the left join.

SELECT 
    COUNT(`info_betting`.`ib_idx`) AS `cnt`
FROM
    `info_betting`
#   LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`
#   LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`
#   LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`
#   LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`
#   LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`
#   LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id`
WHERE
    `info_betting`.`ib_branch_id` = 18
    AND
     `info_betting`.`ib_c_betting_date` >= '2023-10-10'
    AND `info_betting`.`ib_c_betting_date` <= '2023-10-25';
    AND `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-10' AND '2023-10-25';

expain

1   SIMPLE  info_betting    range   info_betting_ib_branch_id_IDX,info_betting_ib_c_betting_date_IDX,info_betting_ib_dbupgt_IDX,info_betting_bd_IDX,info_betting_ib_division_IDX    info_betting_bd_IDX 10  NULL    9510853 Using where; Using index

cnt 5,838,584
time 3.311 s

And I tested this after removing the left join users as partners.

SELECT 
    COUNT(`info_betting`.`ib_idx`) AS `cnt`
FROM
    `info_betting`
    LEFT JOIN `users` ON `users`.`id` = `info_betting`.`ib_user_id`
#   LEFT JOIN users AS partners ON `partners`.`id` = `users`.`partner_id`
    LEFT JOIN `user_etc_infos` ON `user_etc_infos`.`uei_user_id` = `info_betting`.`ib_user_id`
    LEFT JOIN `casino_games` ON `casino_games`.`id` = `info_betting`.`ib_cg_id`
    LEFT JOIN `casino_tables` ON `casino_tables`.`id` = `info_betting`.`ib_c_table_id`
    LEFT JOIN `casino_providers` ON `casino_providers`.`id` = `info_betting`.`ib_c_provider_id`
WHERE
    `info_betting`.`ib_branch_id` = 18
    AND
     `info_betting`.`ib_c_betting_date` >= '2023-10-10'
    AND `info_betting`.`ib_c_betting_date` <= '2023-10-25';
    AND `info_betting`.`ib_c_betting_date` BETWEEN '2023-10-10' AND '2023-10-25';

expain

1   SIMPLE  info_betting    ref info_betting_ib_branch_id_IDX,info_betting_ib_c_betting_date_IDX,info_betting_ib_dbupgt_IDX,info_betting_bd_IDX,info_betting_ib_division_IDX    info_betting_bd_IDX 5   const   9510282 Using index condition

cnt 5,838,584
time 12.640 s

How can I solve this?
I tried several methods.
I just failed.

please help me!!

allan

Do you have indexes on your joins? What database engine are you using?

Allan

casuist

mariadb innodb
indexes all.. ib_user_id, partner_id... etc
info_betting_bd_IDX -> ib_branch_id, ib_c_betting_date

allan

Thank you. I'm genuinely surprised that the left joins make a difference here - the WHERE condition doesn't use them, so I'd have thought that the query planner could optimise the left joins out, since they aren't needed.

How large are the data sets? And what version of MariaDB are you using?

I'm afraid I'm not sure how much more performance I'll be able to get out of it. If the query takes that long with the join, and you need the join, I don't see I can do too much about it. It might need some input from the MariaDB support folks to understand what the query planner is doing there, why the left joins make a difference and what can be done about it.

Allan