Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
Reply to this topicStart new topic
> Dodanie orderBy znacząco zwalnia zapytanie
Intenso
post 1.05.2023, 21:11:04
Post #1





Grupa: Zarejestrowani
Postów: 7
Pomógł: 0
Dołączył: 16.08.2022

Ostrzeżenie: (0%)
-----


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:
  1. $orderItems = DB::table('order_items')
  2. ->join('orders', 'order_items.id', '=', 'orders.id')
  3. ->join('customers', 'customers.id', '=', 'orders.customer_id')
  4. ->join('payment_methods', 'payment_methods.id', '=', 'orders.payment_method_id')
  5. ->join('shipping_methods', 'shipping_methods.id', '=', 'orders.shipping_method_id')
  6. ->join('marketplaces', 'marketplaces.id', '=', 'orders.marketplace_id')
  7. ->select
  8. (
  9. 'order_items.id as order_item_id',
  10. 'order_items.created_at as order_item_created_at',
  11. 'order_items.name as order_item_name',
  12. 'order_items.quantity as order_item_quantity',
  13. 'order_items.price as order_item_price',
  14. 'customers.name as customer_name',
  15. 'payment_methods.name as payment_method_name',
  16. 'shipping_methods.name as shipping_method_name',
  17. 'marketplaces.name as marketplace_name'
  18. )
  19. ->where(function(Builder $query) {
  20. $query->where('order_items.id', '=', $this->search)
  21. ->orwhere('order_items.name', 'like', '%' . $this->search . '%')
  22. ->orwhere('order_items.quantity', '=', $this->search)
  23. ->orwhere('customers.name', 'like', '%' . $this->search . '%')
  24. ->orwhere('payment_methods.name', 'like', '%' . $this->search . '%')
  25. ->orwhere('shipping_methods.name', 'like', '%' . $this->search . '%')
  26. ->orwhere('marketplaces.name', 'like', '%' . $this->search . '%');
  27. })
  28. ->orderBy($this->sortField, $this->sortDirection)
  29. ->limit(100)
  30. ->get();


i generuje to takie zapytanie:
  1. 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?
Go to the top of the page
+Quote Post
Tomplus
post 1.05.2023, 23:09:06
Post #2





Grupa: Zarejestrowani
Postów: 1 828
Pomógł: 225
Dołączył: 20.03.2005
Skąd: Będzin

Ostrzeżenie: (0%)
-----


Może problem leży po stronie kluczy (ich braku) w bazie danych?
Go to the top of the page
+Quote Post
Intenso
post 2.05.2023, 07:15:36
Post #3





Grupa: Zarejestrowani
Postów: 7
Pomógł: 0
Dołączył: 16.08.2022

Ostrzeżenie: (0%)
-----


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.
Go to the top of the page
+Quote Post
trueblue
post 2.05.2023, 07:56:03
Post #4





Grupa: Zarejestrowani
Postów: 6 761
Pomógł: 1822
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


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


--------------------
Go to the top of the page
+Quote Post
Intenso
post 2.05.2023, 09:09:28
Post #5





Grupa: Zarejestrowani
Postów: 7
Pomógł: 0
Dołączył: 16.08.2022

Ostrzeżenie: (0%)
-----


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.
Go to the top of the page
+Quote Post
trueblue
post 2.05.2023, 09:16:16
Post #6





Grupa: Zarejestrowani
Postów: 6 761
Pomógł: 1822
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


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.


--------------------
Go to the top of the page
+Quote Post
Intenso
post 2.05.2023, 09:40:34
Post #7





Grupa: Zarejestrowani
Postów: 7
Pomógł: 0
Dołączył: 16.08.2022

Ostrzeżenie: (0%)
-----


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ąć.
Go to the top of the page
+Quote Post
trueblue
post 2.05.2023, 09:58:22
Post #8





Grupa: Zarejestrowani
Postów: 6 761
Pomógł: 1822
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


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


--------------------
Go to the top of the page
+Quote Post
Intenso
post 2.05.2023, 18:46:52
Post #9





Grupa: Zarejestrowani
Postów: 7
Pomógł: 0
Dołączył: 16.08.2022

Ostrzeżenie: (0%)
-----


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
Go to the top of the page
+Quote Post
nospor
post 3.05.2023, 11:26:03
Post #10





Grupa: Moderatorzy
Postów: 36 440
Pomógł: 6290
Dołączył: 27.12.2004




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


--------------------

"Myśl, myśl, myśl..." - Kubuś Puchatek || "Manual, manual, manual..." - Kubuś Programista
"Szukaj, szukaj, szukaj..." - Kubuś Odkrywca || "Debuguj, debuguj, debuguj..." - Kubuś Developer

Go to the top of the page
+Quote Post
trueblue
post 3.05.2023, 12:46:36
Post #11





Grupa: Zarejestrowani
Postów: 6 761
Pomógł: 1822
Dołączył: 11.03.2014

Ostrzeżenie: (0%)
-----


Cytat(Intenso @ 2.05.2023, 19:46:52 ) *
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ć?

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

Cytat(Intenso @ 2.05.2023, 19:46:52 ) *
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.

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

Cytat(Intenso @ 2.05.2023, 19:46:52 ) *
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ń?

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


--------------------
Go to the top of the page
+Quote Post
viking
post 3.05.2023, 17:05:17
Post #12





Grupa: Zarejestrowani
Postów: 6 365
Pomógł: 1114
Dołączył: 30.08.2006

Ostrzeżenie: (0%)
-----


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


--------------------
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 



RSS Wersja Lo-Fi Aktualny czas: 28.03.2024 - 10:24