1

For some background lets say I have two tables:

create table orders (
 id int unsigned auto_increment primary key,
 uuid char(36) not null,
 status_id int unsigned not null,
 account_id int unsigned not null,
 received_at datetime null,
 released_at datetime null,
 created_at timestamp default CURRENT_TIMESTAMP not null,
 updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
 deleted_at timestamp null,
 constraint orders_account_id_foreign
    foreign key (account_id) references accounts (id),
    foreign key (status_id) references order_statuses (id) );

create index orders_account_id_index
                on orders (account_id);

create index orders_status_id_index
    on orders (status_id);


create table order_items (
 id int unsigned auto_increment primary key,
 order_id int unsigned not null,
 item varchar(255) not null,
 comment text null,
 created_at timestamp  default CURRENT_TIMESTAMP not null,
 updated_at timestamp  default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
 deleted_at timestamp null,
 constraint order_items_order_id_foreign
   foreign key (order_id) references orders (id) on delete cascade );

create index order_items_deleted_at_id_index
         on order_items (deleted_at, id);

create index order_items_order_id_index
         on order_items (order_id);

There are more fields available for these two tables, but I wanted to give a basic example. The following query is called from the application but suffers in performance on the order by portion.

select *
from `order_items`
where (
    exists (  select *
              from `orders`
              where `order_items`.`order_id` = `orders`.`id`
              and (`orders`.`deleted_at` is null)
             and `orders`.`deleted_at` is null
             )
     )
and `order_items`.`deleted_at` is null
order by `order_items`.`created_at` asc, `order_items`.`id` asc
limit 100 offset 0

The problem is that the application allow for sorting by different columns in the order by:

order by
  `this could be any one column` in either direction,
  `order_items`.`id` asc

What would be the best way to optimize something like this? Would an index be needed for any sortable column from the application?

1

2 Answers 2

1

First, simplify the query to

SELECT  oi.*
    FROM  order_items AS oi
    JOIN  orders AS o  ON oi.order_id = o.id
    WHERE  o.deleted_at IS NULL
      AND  oi.deleted_at IS NULL
    ORDER BY  ...
    LIMIT  100 OFFSET 0;

Then, for the ORDER BY..., have both parts saying ASC or both saying DESC. And have a 3-column index:

INDEX(deleted_at, foo, id)

in that order. Don't worry about ASC vs DESC in the INDEX.

The query looks strange -- it seems to return a bunch of order_items from various orders. And, since there is nothing else you are getting from orders, why even mention that table?? So...

SELECT  oi.*
    FROM  order_items AS oi
    WHERE  oi.deleted_at IS NULL
    ORDER BY  ...
    LIMIT  100 OFFSET 0;

(I am assuming the deleted_at flag propagates adequately between the tables?)

2
  • thanks for the help! We need to know if the order is deleted (rather than just some items) which is why that was included. Wouldn't the exist be more appropriate than the joins since we aren't requiring anything from the join?
    – jhdba113
    May 22, 2023 at 0:33
  • @jhdba113 - You say that "deleting" and "order" flags orders, but makes no changes to order_items, correct? If so, then it can be written with EXISTS, LEFT JOIN, or JOIN. And probably the Optimizer will perform each of those the same way.
    – Rick James
    May 22, 2023 at 4:55
-2

I think it suffers because of the sub-select.

  1. Try to reorganise the query to not use a sub-select
  2. Provide indexes for the most-often used order-by tuplets or the ones that return the most number of rows.

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.