Improve performance of cart order ID selection for anonymous users

Can you do an EXPLAIN of the query in question? A query like this should always be fast, we might be missing an index?

Patch looks good. Ordering ensures the latest matching order number comes first.

@bojanz: We have a 50Gig table so, yes, it's slow. We don't need you to merge this patch into the branch but we need it for composer to apply it into the deployed code.

There is no porting to be done here.

Anyway, waiting on the EXPLAIN.

Good deal; I understand - I've posted patches to the queue to use in drush make before as well. : )

fwiw, patch looked good to me on initial review. I think the test fails might be unrelated.

This query just needs an proper index as bojan suspected.

MariaDB [eikon]> explain SELECT order_id FROM commerce_order WHERE order_id IN (1,2,3,4,5) AND uid = 0 AND status IN (1, 2) ORDER BY order_id DESC;
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | commerce_order | ref  | PRIMARY,uid   | uid  | 4       | const |    5 | Using where |
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+

MariaDB [eikon]> show indexes in commerce_order;
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| commerce_order |          0 | PRIMARY      |            1 | order_id     | A         |      314899 |     NULL | NULL   |      | BTREE      |         |               |
| commerce_order |          0 | order_number |            1 | order_number | A         |      314899 |     NULL | NULL   | YES  | BTREE      |         |               |
| commerce_order |          0 | revision_id  |            1 | revision_id  | A         |      314899 |     NULL | NULL   | YES  | BTREE      |         |               |
| commerce_order |          1 | uid          |            1 | uid          | A         |       62979 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Ideally a specific index of order_id, uid and status, in that order.

I don't have anything to replicate the original slow query (mine seem to be fast anyway since I either don't have or don't have many anonymous orders) but I do see a dramatic before and after when filtering by status. Query time for admin/commerce/orders with an exposed filter for order status went from 503.85 ms to 1.38ms.

Before:

MariaDB> EXPLAIN SELECT order_id FROM commerce_order WHERE order_id IN (1,2,3,4,5) AND uid = 0 AND status IN (1, 2) ORDER BY order_id DESC;
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table          | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | commerce_order | ref  | PRIMARY,uid   | uid  | 4       | const |    5 | Using where |
+------+-------------+----------------+------+---------------+------+---------+-------+------+-------------+

After:

mysql> EXPLAIN SELECT order_id FROM commerce_order WHERE order_id IN (1,2,3,4,5) AND uid = 0 AND status IN (1, 2) ORDER BY order_id DESC;
+----+-------------+----------------+------------+-------+----------------------------------------+---------------------+---------+------+------+----------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys                          | key                 | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------------+------------+-------+----------------------------------------+---------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | commerce_order | NULL       | range | PRIMARY,uid,status,order_id_uid_status | order_id_uid_status | 8       | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+----------------+------------+-------+----------------------------------------+---------------------+---------+------+------+----------+--------------------------+

This new patch is just a teeny change to the update description: fixing a typo, adding a period, and changing how the table name is written to match previous update descriptions.

I can confirm that the patch in #15 makes queries such as select order_id, status from commerce_order where created < :time and status in ('cart', 'checkout_checkout', 'checkout_review') limit 20 much faster. (This is a query we run in Cron to expire very old orders.)

Let's please get this in soon. Thanks!

Thanks guys! Committed.