Dodanie orderBy znacząco zwalnia zapytanie Opcje

[Intenso]

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?

[Intenso]

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ń?

Ten post edytował Intenso 2.05.2023, 18:50:09