skip to Main Content

I have a problem with a relatively simple MySQL query. In the database, I have about 300k records. The following query takes about 1-2 milliseconds to execute. However, after adding an ORDER BY clause to the query, the execution time increases to over 700 milliseconds. The database has indexes set up on the following tables:

customers.id, 
marketplaces.id, 
orders.customer_id, 
orders.shipping_method_id, 
orders.payment_method_id, 
orders.marketplace_id, 
order_items.id, 
order_items.order_id, 
shipping_methods.id

What should I change in this query to reduce its execution time? The query contains so many OR WHERE clauses because it is connected to an input with a live search feature.

SELECT
    `order_items`.`id` AS `order_item_id`,
    `order_items`.`created_at` AS `order_item_created_at`,
    `order_items`.`name` AS `order_item_name`,
    `order_items`.`quantity` AS `order_item_quantity`,
    `order_items`.`price` AS `order_item_price`,
    `customers`.`name` AS `customer_name`,
    `payment_methods`.`name` AS `payment_method_name`,
    `shipping_methods`.`name` AS `shipping_method_name`,
    `marketplaces`.`name` AS `marketplace_name`
FROM
    `order_items`
INNER JOIN `orders` ON `order_items`.`order_id` = `orders`.`id`
INNER JOIN `customers` ON `customers`.`id` = `orders`.`customer_id`
INNER JOIN `payment_methods` ON `payment_methods`.`id` = `orders`.`payment_method_id`
INNER JOIN `shipping_methods` ON `shipping_methods`.`id` = `orders`.`shipping_method_id`
INNER JOIN `marketplaces` ON `marketplaces`.`id` = `orders`.`marketplace_id`
WHERE
    (
        `order_items`.`id` = '' OR `order_items`.`name` LIKE '%%' OR `order_items`.`quantity` = '' OR `customers`.`name` LIKE '%%' OR `payment_methods`.`name` LIKE '%%' OR `shipping_methods`.`name` LIKE '%%' OR `marketplaces`.`name` LIKE '%%'
    )
ORDER BY
    `order_item_id` ASC
LIMIT 100

I tried removing all the search conditions, but it had little effect on the search speed.

2

Answers


  1. Consider trying

    ORDER BY
    `order_items`.`id` ASC
    

    to use the index.

    Login or Signup to reply.
  2. Any column mentioned in your join ON statements or WHERE clauses should be indexed. So add an index on the following columns as well:

    `customers`.`name`
    `marketplaces`.`name`
    `order_items`.`name`
    `order_items`.`quantity`
    `orders`.`id`
    `payment_methods`.`id`
    `payment_methods`.`name`
    `shipping_methods`.`name`
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search