Pomoc se zrychlením MySQL dotazu

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ě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 0díky za nápady

Indexy nastavené máš?

Jaký je výstup z explain?

jj indexy mám, výstup z explain: 29110

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

Díky za snahu, je to o chlup rychlejší, ale ještě by to určitě chtělo vylepšit.

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 chybyselect 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

tam je problém, že tabulka tour má skoro 700k záznamů a když se takto hodí join, tak zpracování trvá až 27s :)

Hm... No tak jsem moc nepomoh :) kdyby me neco napadlo, dam.vedet.

Zkus pouzit fce benchmark() a explain. Pohraj si s tim. Btw jakou verzi mysql mas?

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 0Pokud 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_priceALTER 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 0Pomocí EXPLAIN zkontrolovat, že se využije hotels_category_date_from_id_index

díky, vyzkouším

@takatom napsal normalni reseni, btw.@fida8 jakou verzi MySQL mas? V takovem poctu dotazu muze novejsi verze to cele zrychlit.

je tam verze 5.0.12

Dovíme se, jak to dopadlo?