0

Any idea how to optimise this query?

It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.

Problem is, that I need to been able to order the result, using the results from the sub-queries. Need to been able to order by: num_products, credits_ordered, credits_consumed, num_refunds, amount_refunded, last_uploaded_date and last_order_date.

The query:

SELECT DISTINCT `users`.`id` as `user_id`, `users`.`email` as `user_email`, 

(select count(products.id) from products where products.user_id = users.id and products.added >= '2021-09-29' and products.added < '2021-10-29') as num_products, 

(select COALESCE(sum(amount), 0) from credits_history where credits_history.user_id = users.id and credits_history.type = 1 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') as credits_ordered, 

(select COALESCE(sum(amount), 0) from credits_history where credits_history.user_id = users.id and credits_history.type = 2 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') as credits_consumed, 

COALESCE(sum((select count(id) from credits_history where credits_history.user_id = users.id and credits_history.type = 3 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0)
+
COALESCE(sum((select count(id) from credits_history where credits_history.user_id = users.id and credits_history.type = 4 and credits_history.amount > 0 and credits_history.order_id IS NULL and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29') ), 0) as num_refunds, 

COALESCE(sum((select sum(amount) from credits_history where credits_history.user_id = users.id and credits_history.type = 3 and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0) 
+
COALESCE(sum((select sum(amount) from credits_history where credits_history.user_id = users.id and credits_history.type = 4 and credits_history.amount > 0 and credits_history.order_id IS NULL and credits_history.credits_type = 1 and credits_history.added >= '2021-09-29' and credits_history.added < '2021-10-29')), 0) as amount_refunded, 

(select added from products where products.user_id = users.id order by id desc limit 1) as last_uploaded_date, 
(select added from credits_history where credits_history.user_id = users.id and credits_history.type = 1 order by credits_history.id desc limit 1) as last_order_date
FROM `users`
WHERE `users`.`is_subuser` =0
GROUP BY `user_id`
ORDER BY `num_products` DESC
LIMIT 0,10

Database schema:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `is_subuser` tinyint(1) DEFAULT '0',
  `customer_type` tinyint(1) DEFAULT '1',
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `is_subuser` (`is_subuser`)
) ENGINE=InnoDB AUTO_INCREMENT=25006 DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `is_user_id_is_deleted` (`id`,`user_id`,`is_deleted`),
  KEY `user_id_is_deleted` (`user_id`,`is_deleted`),
  KEY `id_is_deleted` (`id`,`is_deleted`),
  KEY `added` (`added`)
) ENGINE=InnoDB AUTO_INCREMENT=196921 DEFAULT CHARSET=utf8;


CREATE TABLE `credits_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `credits_type` tinyint(1) NOT NULL,
  `order_id` bigint(11) DEFAULT NULL,
  `added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `user_id_credits_type_added` (`user_id`,`credits_type`,`added`),
   KEY `order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=176204 DEFAULT CHARSET=utf8;

Explain:

        +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+
    | id | select_type        | table           | partitions | type  | possible_keys                                                                   | key                        | key_len | ref            | rows | filtered | Extra                                                  |
    +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+
    |  1 | PRIMARY            | users           | NULL       | range | PRIMARY,can_login,is_subuser_master_id,is_subuser,magento_customer_id,master_id | is_subuser                 | 2       | NULL           | 6720 |   100.00 | Using index condition; Using temporary; Using filesort |
    | 10 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_credits_type_added | 4       | func           |   85 |    10.00 | Using index condition; Using where; Using filesort     |
    |  9 | DEPENDENT SUBQUERY | products        | NULL       | ref   | user_id_is_deleted                                                              | user_id_is_deleted         | 4       | func           |   79 |   100.00 | Using index condition; Using filesort                  |
    |  8 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id,order_id                    | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.06 | Using index condition; Using where                     |
    |  7 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.11 | Using index condition; Using where                     |
    |  6 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id,order_id                    | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.06 | Using index condition; Using where                     |
    |  5 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | tf_db.users.id |   90 |     0.11 | Using index condition; Using where                     |
    |  4 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | func           |   90 |     0.11 | Using index condition; Using where                     |
    |  3 | DEPENDENT SUBQUERY | credits_history | NULL       | ref   | user_id,user_id_credits_type_added,user_id_slave_id                             | user_id_slave_id           | 4       | func           |   90 |     0.11 | Using index condition; Using where                     |
    |  2 | DEPENDENT SUBQUERY | products        | NULL       | ref   | user_id_is_deleted,added                                                        | user_id_is_deleted         | 4       | func           |   79 |     0.93 | Using index condition; Using where                     |
    +----+--------------------+-----------------+------------+-------+---------------------------------------------------------------------------------+----------------------------+---------+----------------+------+----------+--------------------------------------------------------+

For the sake of simplicity, I have removed some fields/indexes from the table schema, because tables are with lots of fields.

7
  • 1
    As a workaround at least, are you able to select the results to a TEMPORARY TABLE first, and then select from that table with the ORDER BY clause?
    – J.D.
    Commented Oct 29, 2021 at 12:38
  • @J.D. I didn't tried that. Whats your idea? Create temporary table and store all the results there (without any ordering) and then query that table and order against it? Commented Oct 29, 2021 at 12:43
  • Yes, exactly my thoughts. If your performance issue is due to you adding the ORDER BY clause, it's probably the query plan being generated to fetch the data and sort it that's the issue. If you persist the results into a TEMPORARY TABLE first without sorting, you'll probably get a different query plan. Then selecting from your TEMPORARY TABLE with a simple sort should be performant.
    – J.D.
    Commented Oct 29, 2021 at 12:53
  • @J.D. I tested it but it doesn't speed it up. Creating the temporary table is also slow - 13600 rows were added for about 2-5 seconds. Then query and ordering against the temp table is blazing fast ofc. But the overall issue still exists :( Commented Oct 29, 2021 at 13:10
  • "credits_history.added >= '2021-09-29' and credits_history.added < '2018-07-01'" is only possible in Sugarmounts VetaMerse. Commented Oct 29, 2021 at 13:26

2 Answers 2

1

I doubt if there is any reason to use both DISTINCT and GROUP BY in the same query.

Let's turn the query inside-out. However, this will add complexity -- namely that you will need to have a different query for each of the different ORDER BYs.

Currently, the query is what I call "explode-implode". It hits lots of rows in lots of tables, only to later boil the result down to 10 rows. We need to minimize the effort of finding ids for the 10 rows, then do all the other work.

The gist is

SELECT ids.user_id,
       ids.num_products,
       ( ... ) AS credits_ordered,
       ( ... ) AS ...,
       ( ... ) AS ...,
       COALESCE( ... ) AS ...,
       COALESCE( ... ) AS ...
    FROM (
        SELECT users.id AS user_id
            FROM `users`, num_products
            JOIN .... (( to get num_products ))
            GROUP BY users.id
            ORDER BY `num_products` DESC
            LIMIT 0,10
         ) AS ids
    ORDER BY ids.num_products DESC

The id is that a lot of rows may need to be touched to get the ids subquery, but only 10 rows need to be touched to get each of the other subqueries.

Another approach (which may work better or may work worse):

All the rest seem to depend on

            from  credits_history
            where  credits_history.user_id = users.id
              and  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'

(plus tests on type and amount)

So, try to use

   JOIN ( SELECT user_id, type, amount, credits_history
            FROM credits_history
            WHERE  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'
        ) AS ch
      ON  ch.user_id = users.id

Then stick type and amount in the subqueries.

A third idea is to do something like (given that you are using 8.0 or 10.2)

WITH ch AS
    ( SELECT user_id, type, amount, credits_history
            FROM credits_history
            WHERE  credits_history.credits_type = 1
              and  credits_history.added >= '2021-09-29'
              and  credits_history.added <  '2021-10-29'
    )

Meanwhile, some of these may be helpful:

users:  INDEX(is_subuser,  id, email)
products:  INDEX(user_id, added,  id)
credits_history:  INDEX(user_id, credits_type, type, added)
credits_history:  INDEX(user_id, type, amount, order_id, credits_type, added)

Clean up some indexes:

  KEY `user_id` (`user_id`),  -- Drop because redundant with the following:
  KEY `user_id_credits_type_added` (`user_id`, `credits_type`, `added`),

  KEY ... (id, ...)  -- Drop because redundant with PRIMARY KEY(id)

In general, use COUNT(*) instead of COUNT(id).

1
  • Thanks for your reply. On the 1st approach ... JOIN .... (( to get num_products )) - what is the exact join that I need to use? JOIN products ON products.user_id = users.id doesn't seems to work. Commented Nov 1, 2021 at 11:33
1

Your explain plan looks like it's reading from the credits_history table 7 times. In general, it's better to only read from the table once if you can help it. Multiple reads will definitely add a lot of run time to a query.

I would try rewriting it so that you read only once from credits_history. Make the where clause general enough to read all the rows that you need in all the groups at once. Then do conditional summation after the fact with those rows.

Something like this. (I'm using Postgresql so syntax may be off a little)

select
  c.result_one,
  c.result_one
  ...
from 
`users` u
left join lateral (
  select
    sum(case when c.history_type = 1 then c.amount else 0 end) as result_one,
    sum(case when c.history_type = 2 and c.some_other_thing = 'foo' then c.amount else 0) as result_two,
  from `credits_history` c
  where 
    c.user_id = u.id
    ...--set the rest of the where clause
       --such that you read all credits_history rows that will be included anywhere in your dataset
) c on true
--and your other joins as they make sense results. make sure it could use indexes if appropriate.
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.