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
Consider trying
to use the index.
Any column mentioned in your join ON statements or WHERE clauses should be indexed. So add an index on the following columns as well: