Cześć,
mam tabelę którą wypełniłem seederem i łącznie mam w niej około 500k rekordów. W Laravel generuję query builderem coś takiego:
$orderItems = DB::table('order_items')
->join('orders', 'order_items.id', '=', 'orders.id')
->join('customers', 'customers.id', '=', 'orders.customer_id')
->join('payment_methods', 'payment_methods.id', '=', 'orders.payment_method_id')
->join('shipping_methods', 'shipping_methods.id', '=', 'orders.shipping_method_id')
->join('marketplaces', 'marketplaces.id', '=', 'orders.marketplace_id')
->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'
)
->where(function(Builder $query) {
$query->where('order_items.id', '=', $this->search)
->orwhere('order_items.name', 'like', '%' . $this->search . '%')
->orwhere('order_items.quantity', '=', $this->search)
->orwhere('customers.name', 'like', '%' . $this->search . '%')
->orwhere('payment_methods.name', 'like', '%' . $this->search . '%')
->orwhere('shipping_methods.name', 'like', '%' . $this->search . '%')
->orwhere('marketplaces.name', 'like', '%' . $this->search . '%');
})
->orderBy($this->sortField, $this->sortDirection)
->limit(100)
->get();
i generuje to takie zapytanie:
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`.`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_items`.`id` DESC LIMIT 100
Problem z tym wszystkim jest takie, że dodanie order by zwiększa czas wykonywania zapytania z 1,5ms na ponad 500ms co jest kolosalną różnicą. Powyższe zapytanie jest używane do livesearch. Co tutaj jest nie tak, że dodanie sortowanie aż tak mocno zwalnia zapytanie?