logo
27.05.2019 19:46
1
Zdravím,

měl by někdo nápad jak přebudovat a zrychlit tento SQL dotaz? Mám 3 tabulky hotels (22k řádků), categories (273) a tours (690k), zpracování trvá cca od 0.8s až 1.2s, což se mi zdá hodně

Kód:
select 
    hotels.id as h_id, 
    hotels.name as h_name, 
    hotels.main_image, 
    hotels.stars, 
    hotels.rating, 
    hotels.rating_count, 
    hotels.gps, c.id as c_id, 
    c.name as c_name, 
    c.url as c_url, 
    c.id_parent, 
    c.lft, c.level, 
    (select min(price) from tours where tours.id_hotel = hotels.id limit 1) as m_price 
from 
    hotels join categories c on hotels.id_category = c.id 
where 
    exists (SELECT id FROM tours WHERE id_hotel = hotels.id limit 1) AND c.lft >= 123 AND c.rgt <= 164 
order by 
    hotels.date_from, hotels.id asc 
limit 10 offset 0
díky za nápady

Co se právě děje na Webtrhu?

27.05.2019 20:01
2
Indexy nastavené máš?
27.05.2019 20:02
3
Jaký je výstup z explain?
27.05.2019 20:09
4
jj indexy mám, výstup z explain:

Název:  db.jpg
Zobrazení: 315
Velikost:  42,5 KB
27.05.2019 21:01
5
Nejsem odbornik na databze, takze budu hadat -
Dela to ten subquery ve wheru, spusti se 22 tisickrat, aby se mohlo vyhodnotit jaky radky zobrazit a to proste trva. Asi bych to joinnul a groupnul podle hotel_id, asi to taky neni optimalni (nevim) ale aspon to nebude delat desetitisice subqueries
27.05.2019 21:13
6
Díky za snahu, je to o chlup rychlejší, ale ještě by to určitě chtělo vylepšit.
27.05.2019 21:36
7
co zkusit joinnout ten subquery? nedelal yb se pak ani subquery ve vysledku a omezilo by to rapidne pocet joinovanych radek... mozna by to pomohlo... pisu z hlavy, neoveruju, tak muze obsahovat chyby
Kód:
select 
    hotels.id as h_id, 
    hotels.name as h_name, 
    hotels.main_image, 
    hotels.stars, 
    hotels.rating, 
    hotels.rating_count, 
    hotels.gps, c.id as c_id, 
    c.name as c_name, 
    c.url as c_url, 
    c.id_parent, 
    c.lft, c.level, 
    tours.m_price 
from 
    hotels 
    join categories c on hotels.id_category = c.id 
    join (SELECT id_hotel, min(price) as m_price from tours GROUP BY id_hotel) as tours on (tours.id_hotel=hotels.id)
where 
   c.lft >= 123 AND c.rgt <= 164 
order by 
    hotels.date_from, hotels.id asc 
limit 10 offset 0
27.05.2019 21:42
8
tam je problém, že tabulka tour má skoro 700k záznamů a když se takto hodí join, tak zpracování trvá až 27s :)
27.05.2019 21:48
9
Hm... No tak jsem moc nepomoh :) kdyby me neco napadlo, dam.vedet.
27.05.2019 22:01
10
Zkus pouzit fce benchmark() a explain. Pohraj si s tim. Btw jakou verzi mysql mas?
28.05.2019 08:39
11
select hotels.id as h_id, hotels.name as h_name, hotels.main_image, hotels.stars,
hotels.rating, hotels.rating_count, hotels.gps, c.id as c_id, c.name as c_name,
c.url as c_url, c.id_parent, c.lft, c.level,
(select min(price) from tours where tours.id_hotel = hotels.id) as m_price
from categories c join hotels on hotels.id_category = c.id
where c.lft >= 123 AND c.rgt <= 164
and exists (SELECT id FROM tours WHERE id_hotel = hotels.id)
order by hotels.date_from, hotels.id asc limit 10 offset 0

Pokud to nebude dost rychlé:
ALTER TABLE hotels ADD INDEX hotels_category_date_from_id_index (id_category,date_from,id)

Anebo pokud oba dotazy vracejí stejnou množinu pro libovolný interval
(select id from category where lft >= 123 AND rgt <= 164)
(select id from category where id between 123 AND 164)

Doplnit do tabulky hotels atribut min_price
ALTER TABLE hotels ADD COLUMN (min_price DECIMAL)

ten se bude plnit trigrem nad tabulkou tours:

DELIMITER $$
CREATE TRIGGER tours_insert_trg
AFTER INSERT ON tours
FOR EACH ROW
begin
update hotels h set min_price = NEW.price where h.id = NEW.id_hotel AND (h.min_price IS NULL OR h.min_price < NEW.price;
end$$

CREATE TRIGGER tours_update_trg
AFTER UPDATE ON tours
FOR EACH ROW
begin
update hotels h set min_price = NEW.price where h.id = NEW.id_hotel AND (h.min_price IS NULL OR h.min_price < NEW.price;
end$$
DELIMITER ;

A celý dotaz se pak zjednoduší:
select hotels.id as h_id, hotels.name as h_name, hotels.main_image, hotels.stars,
hotels.rating, hotels.rating_count, hotels.gps, c.id as c_id, c.name as c_name,
c.url as c_url, c.id_parent, c.lft, c.level,
min_price as m_price
from hotels join categories c on hotels.id_category = c.id
where hotels.id_category BETWEEN 123 AND 164 AND min_price IS NOT NULL
order by hotels.date_from, hotels.id asc limit 10 offset 0

Pomocí EXPLAIN zkontrolovat, že se využije hotels_category_date_from_id_index
28.05.2019 09:05
12
díky, vyzkouším
28.05.2019 10:23
13
@takatom napsal normalni reseni, btw.

@fida8 jakou verzi MySQL mas? V takovem poctu dotazu muze novejsi verze to cele zrychlit.
28.05.2019 15:22
14
je tam verze 5.0.12
01.06.2019 09:39
15
Dovíme se, jak to dopadlo?
03.06.2019 20:58
16
udělal jsem to dle toho návodu a dobrý, díky