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:

[PHP] pobierz, plaintext 
$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();
[PHP] pobierz, plaintext 

i generuje to takie zapytanie:

[SQL] pobierz, plaintext 
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
[SQL] pobierz, plaintext 

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?

Może problem leży po stronie kluczy (ich braku) w bazie danych?

Ustawiłem indeksy na wyszukiwanych kolumnach

ALTER TABLE order_items ADD INDEX idx_order_items_id (id);
ALTER TABLE order_items ADD INDEX idx_order_items_name (name);
ALTER TABLE order_items ADD INDEX idx_order_items_quantity (quantity);
ALTER TABLE customers ADD INDEX idx_customers_name (name);
ALTER TABLE payment_methods ADD INDEX idx_payment_methods_name (name);
ALTER TABLE shipping_methods ADD INDEX idx_shipping_methods_name (name);
ALTER TABLE marketplaces ADD INDEX idx_marketplaces_name (name);

mimo, że np. na name używam like %% to postanowiłem w celach testowych też założyć indeks. Niestety po wprowadzeniu tych zmian zapytania wykonują się w identycznym czasie.

A w tabeli orders są założone klucze?

Tak, orders posiada indeksy na customer_id, shipping_method_id, payment_method_id oraz marketplace_id.
Jeszcze teraz zauważyłem, że tutaj jest błąd: ->join('orders', 'order_items.id', '=', 'orders.id') bo powinno być order_items.order_id natomiast po dodaniu tego czas wykonywania zapytania z orderBy zwiększył się do 1s a bez niego w dalszym ciągu jest mniej niż 2 milisekundy.

Wykonaj EXPLAIN SELECT... bezpośrednio na bazie. Raz z ORDER BY, drugi raz bez.

P.S. Przy braku wypełnienia ilości otrzymasz warunek, który nigdy nie będzie spełniony: `order_items`.`quantity` = ''
P.P.S. Warto sprawdzać wypełnienie kryteriów i budować zapytanie zależne od tego czy kryterium jest puste czy nie.

Tak wygląda z orderBy



a tak bez:



Szczerze mówiąc pierwszy raz mam do czynienia z zapytaniami z query buildera, w większości używałem Eloquenta i nie za bardzo wiem jakie wnioski mogę z tych explain selectów wyciągnąć.

Spróbuj sprawdzić zapytanie bez warunków OR (z ORDER BY). Podejrzewam, że OR może być tu problemem.

Usunięcie wszystkich warunków zmniejsza czas zapytania o jakieś 50 milisekund. Niestety mimo wszystko 2-3ms bez order by do 700-1000ms przy order by to ogromna przepaść.

Siedziałem dzisiaj nad tym cały dzień i mam kilka pytań.
Generalnie MySQL zainstalowałem z repozytoriów brew i nic więcej w nim nie zmieniałem jeżeli chodzi o konfigurację. Stwierdziłem, że coś z tym silnikiem jest nie tak i postanowiłem również przez brew zainstalować PostgreSQL. Obydwa silniki mają domyślną konfigurację.

1. MySQL zapytanie bez order by wykonuje w 1-2ms, z order by 1200ms
2. PostgreSQL bez różnicy czy dodam order by czy nie to zawsze zapytanie zajmuje od 10-20ms

Z PostgreSQL usunąłem z zapytania część, gdzie było wyszukiwanie bigintów bo postgresql miał z tym problem ale akurat to nie robiło wcześniej żadnej różnicy.
Na początku baza ważyła około 100mb ale byłem zaciekawiony jak PostgreSQL się zachowa z większą ilością i wypełniałem bazę tak długo aż baza ważyła 1GB. Ku mojemu zdziwieniu te zapytania nadal wykonywały się 10-20ms.

Zauważyłem jednak, że gdy wyszukuję wszystko czyli % % jest puste to zapytanie trwa 10-20ms. Dodam jakiś tekst który znajduje się w bazie to ten czas zwiększa się do 200-300ms. Gdy wyszukam coś czego nie ma w bazie to czas wydłuża się nawet do 3 sekund. Czym to jest spowodowane i jak można to naprawić?

Jest to moja pierwsza aplikacja w której zależy mi aby wszystko działało płynnie. Czy jeżeli chodzi o livesearch to takie czasy wyszukiwania są akceptowalne? Oczywiście wszystko mam uruchomione u siebie na komputerze z domyślnymi ustawieniami.

I zauważyłem jeszcze jedną rzecz która jest moim zdaniem absurdalna. Kiedy w tle nic się nie dzieje to zapytania wykonują się tak jak wcześniej napisałem w 10-20ms. Gdy seeduje bazę danych ogromną ilością zapytań to baza jakby włączała jakieś turbo i te same zapytania wykonują się w 3-5 ms. To jest normalne czy coś mi się dzieje z licznikiem wykonywania zapytań?

1) jak juz mowiono, jak nie okresliles search to nie produkuj WHERE ktore po search szukaja.
2) Jak nie musisz joinowac czegos to nei joinuj - patrz punk wyzej
3) Zamien INNER JOIN na LEFT JOIN. Wbrew pozorom to ma znaczenie. Jak LEFT JOIN nie pomoze to sprobuj samo JOIN

Oczywiscie testuj na samym order by bez warunkow.
I jesli to nie pomoze to wymus index ktory ma zostac uzyty, czyli w twoim wypadku wymus index po polu po ktorym sortujesz.

DOdatkowo zalozyleles klucze obce na laczane tabele? Bo to wszystko wyglada jakby bylo robione z lapanki

Zapytania używające LIKE nie korzystają z indeksu, ewentualnie w jakimś stopniu jeśli warunek ma postać '....%'.


Używanie do livesearch funkcji LIKE nie jest dobrym pomysłem.


Jeśli to te same zapytanie, to być może wynik jest podawany z cache bazy.


Spróbuj zmienić na: ORDER BY `orders`.`id` DESC

Albo jeśli to livesearch zastosuj typowe do tego rozwiązanie np meilisearch które laravel ładnie wspiera.