我有一个CPU killing查询，需要10秒以上的时间来执行：

select 
  sum(t.total) payment_amount, 
  sum(
    IFNULL(
      t.credit, group_wallets_transactions.credit
    )
  ) cashbacked_amount, 
  case when group_wallets_transactions.type = "daily_cashback"
         or group_wallets_transactions.type = "payment_consume" then "cashback"
       else group_wallets_transactions.type end type, 
  t.business_id as b_id, 
  business_sub_categories.name sub_category, 
  business_sub_categories.selected_img, 
  group_wallets_transactions.created_at as date, 
  businesses.shop_name 
from 
  `group_wallets_transactions` 
  left join (
    (
      select 
        'cash' as pay_type, 
        sum(amount) as total, 
        round(
          sum(daily_percentage * amount / 100), 
          2
        ) as credit, 
        DATE_ADD(
          Date(created_at), 
          INTERVAL 1 DAY
        ) as created_at, 
        `user_id`, 
        `business_id` 
      from 
        `pos_transactions` 
      where 
        (`user_id` = 408670) 
        and `rewarding_model` = 'cash' 
      group by 
        DATE_ADD(
          Date(created_at), 
          INTERVAL 1 DAY
        ), 
        `user_id`, 
        `business_id`
    ) 
    union all 
      (
        select 
          'credit' as pay_type, 
          sum(amount) as total, 
          round(
            sum(daily_percentage * amount / 100), 
            2
          ) as credit, 
          DATE_ADD(
            Date(created_at), 
            INTERVAL 1 DAY
          ) as created_at, 
          `user_id`, 
          `business_id` 
        from 
          `club_transactions` 
        where 
          (`user_id` = 408670) 
          and `customer_cashback_status` = 1 
          and `club_transactions`.`had_payment_reward` = 1 
          and `club_transactions`.`rewarding_model` = 'cash' 
          and `status` = 'paid' 
        group by 
          DATE_ADD(
            Date(created_at), 
            INTERVAL 1 DAY
          ), 
          `user_id`, 
          `business_id`
      )
  ) as `t` on date(
    group_wallets_transactions.created_at
  ) = date(t.created_at) 
  and `group_wallets_transactions`.`type` in ('daily_cashback') 
  left join `businesses` on `businesses`.`id` = `t`.`business_id` 
  left join `business_sub_categories` on `businesses`.`sub_category_id` = `business_sub_categories`.`id` 
where 
  (
    `group_wallets_transactions`.`user_id` = 408670
  ) 
group by 
  `type`, 
  `business_id`, 
  `sub_category`, 
  `selected_img`, 
  `date`, 
  `shop_name` 
order by 
  `group_wallets_transactions`.`created_at` desc

它工作得很好，给了我预期的结果。但是由于表pos_transactions和club_transactions已经变得很大（每个表都有大约100万行），现在上面的查询的执行时间超过了10秒。下面是EXPLAIN的结果：

此外，group_wallets_transactions不是table，它是view，大约6秒的执行时间只是用于运行它，它的逻辑是：

CREATE ALGORITHM = UNDEFINED DEFINER = `administrator` @`%` SQL SECURITY DEFINER VIEW `group_wallets_transactions` AS 
select 
  `wallets_transactions`.`user_id` AS `user_id`, 
  `wallets_transactions`.`type` AS `TYPE`, 
  cast(
    `wallets_transactions`.`created_at` as date
  ) AS `created_at`, 
  sum(
    `wallets_transactions`.`credit`
  ) AS `credit` 
from 
  `wallets_transactions` 
group by 
  `wallets_transactions`.`user_id`, 
  `wallets_transactions`.`type`, 
  cast(
    `wallets_transactions`.`created_at` as date
  )

有什么方法可以优化它吗？

我已经尝试在需要的列上放置一些复合索引，并使性能更好，但仍然需要10秒才能执行，表现得像一个瓶颈。下面是group_wallets_transactions视图的EXPLAIN结果：