search

laravel / pulse

Laravel Pulse is a real-time application performance monitoring tool and dashboard for your Laravel application.
https://pulse.laravel.com
MIT License
1.47k stars 171 forks source link

Rendering of very long slow SQL queries slows down the page #387

Closed chimit closed 5 months ago

chimit commented 5 months ago

Pulse Version

1.2.2

Laravel Version

10.48.12

PHP Version

8.2

Livewire Version

3.5.0

Database Driver & Version

MySQL 8

Description

I have some very long SQL queries (18k symbols) and apparently syntax highlighting of such queries is very resource intensive so the browser freezes all the time (MacBook Air M3).

Steps To Reproduce

Make any very long SQL query (in size and in execution time). E.g. here is mine:

SELECT `properties`.*, (SELECT COUNT(*) FROM `homes` WHERE `properties`.`id` = `homes`.`property_id` AND `listing_id` IS NOT NULL AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AS `homes_count`, (SELECT COUNT(*) FROM `homes` WHERE `properties`.`id` = `homes`.`property_id` AND `listing_id` IS NOT NULL AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND (`ended_at` IS NULL OR `ended_at` > ?)) AND ((`unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR (`unavailable_before_monthly` IS NOT NULL AND `unavailable_before_monthly` <= ?)) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AS `vacant_homes_count_monthly`, ( REPLACE( ( SELECT IF ( available_now_monthly = 1, '1970-01-01', available_date_monthly ) AS min_available_date FROM listings WHERE properties.id = listings.property_id ORDER BY -(min_available_date) DESC LIMIT 1 ), '1970-01-01', NULL ) ) AS available_date_monthly, (SELECT monthly_price_for_sorting FROM `listings` INNER JOIN (SELECT MAX(`listings`.`id`) AS `id_aggregate`, MIN(`listings`.`monthly_price_for_sorting`) AS `monthly_price_for_sorting_aggregate`, `listings`.`property_id` FROM `listings` INNER JOIN (SELECT MIN(`listings`.`monthly_price_for_sorting`) AS `monthly_price_for_sorting_aggregate`, `listings`.`property_id` FROM `listings` WHERE `monthly_price_for_sorting` IS NOT NULL AND EXISTS (SELECT * FROM `homes` WHERE `listings`.`id` = `homes`.`listing_id` AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND `started_at` <= ? AND (`ended_at` IS NULL OR `ended_at` > ?)) AND (((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL) OR (`unavailable_before_nightly` IS NOT NULL AND `unavailable_before_nightly` <= ?)) OR `unavailable_after_nightly` >= ?) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`monthly_price_for_sorting_aggregate` = `listings`.`monthly_price_for_sorting` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `monthly_price_for_sorting` IS NOT NULL AND EXISTS (SELECT * FROM `homes` WHERE `listings`.`id` = `homes`.`listing_id` AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND `started_at` <= ? AND (`ended_at` IS NULL OR `ended_at` > ?)) AND (((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL) OR (`unavailable_before_nightly` IS NOT NULL AND `unavailable_before_nightly` <= ?)) OR `unavailable_after_nightly` >= ?) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`id_aggregate` = `listings`.`id` AND `cheapestListing`.`monthly_price_for_sorting_aggregate` = `listings`.`monthly_price_for_sorting` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `properties`.`id` = `listings`.`property_id` AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) LIMIT 1) AS `cheapest_monthly_available_listing_monthly_price_for_sorting`, (SELECT monthly_price_for_sorting FROM `listings` INNER JOIN (SELECT MAX(`listings`.`id`) AS `id_aggregate`, MIN(`listings`.`monthly_price_for_sorting`) AS `monthly_price_for_sorting_aggregate`, `listings`.`property_id` FROM `listings` INNER JOIN (SELECT MIN(`listings`.`monthly_price_for_sorting`) AS `monthly_price_for_sorting_aggregate`, `listings`.`property_id` FROM `listings` WHERE `monthly_price_for_sorting` IS NOT NULL AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`monthly_price_for_sorting_aggregate` = `listings`.`monthly_price_for_sorting` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `monthly_price_for_sorting` IS NOT NULL AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`id_aggregate` = `listings`.`id` AND `cheapestListing`.`monthly_price_for_sorting_aggregate` = `listings`.`monthly_price_for_sorting` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `properties`.`id` = `listings`.`property_id` AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) LIMIT 1) AS `cheapest_monthly_listing_monthly_price_for_sorting`, (SELECT nightly_price FROM `listings` INNER JOIN (SELECT MAX(`listings`.`id`) AS `id_aggregate`, MIN(`listings`.`nightly_price`) AS `nightly_price_aggregate`, `listings`.`property_id` FROM `listings` INNER JOIN (SELECT MIN(`listings`.`nightly_price`) AS `nightly_price_aggregate`, `listings`.`property_id` FROM `listings` WHERE `nightly_price` IS NOT NULL AND EXISTS (SELECT * FROM `homes` WHERE `listings`.`id` = `homes`.`listing_id` AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND `started_at` <= ? AND (`ended_at` IS NULL OR `ended_at` > ?)) AND (((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL) OR (`unavailable_before_nightly` IS NOT NULL AND `unavailable_before_nightly` <= ?)) OR `unavailable_after_nightly` >= ?) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`nightly_price_aggregate` = `listings`.`nightly_price` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `nightly_price` IS NOT NULL AND EXISTS (SELECT * FROM `homes` WHERE `listings`.`id` = `homes`.`listing_id` AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND `started_at` <= ? AND (`ended_at` IS NULL OR `ended_at` > ?)) AND (((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL) OR (`unavailable_before_nightly` IS NOT NULL AND `unavailable_before_nightly` <= ?)) OR `unavailable_after_nightly` >= ?) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`id_aggregate` = `listings`.`id` AND `cheapestListing`.`nightly_price_aggregate` = `listings`.`nightly_price` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `properties`.`id` = `listings`.`property_id` AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) LIMIT 1) AS `cheapest_nightly_available_listing_nightly_price`, (SELECT nightly_price FROM `listings` INNER JOIN (SELECT MAX(`listings`.`id`) AS `id_aggregate`, MIN(`listings`.`nightly_price`) AS `nightly_price_aggregate`, `listings`.`property_id` FROM `listings` INNER JOIN (SELECT MIN(`listings`.`nightly_price`) AS `nightly_price_aggregate`, `listings`.`property_id` FROM `listings` WHERE `nightly_price` IS NOT NULL AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`nightly_price_aggregate` = `listings`.`nightly_price` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `nightly_price` IS NOT NULL AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) GROUP BY `listings`.`property_id`) AS `cheapestListing` ON `cheapestListing`.`id_aggregate` = `listings`.`id` AND `cheapestListing`.`nightly_price_aggregate` = `listings`.`nightly_price` AND `cheapestListing`.`property_id` = `listings`.`property_id` WHERE `properties`.`id` = `listings`.`property_id` AND EXISTS (SELECT * FROM `properties` WHERE `listings`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) LIMIT 1) AS `cheapest_nightly_listing_nightly_price`, (SELECT COUNT(*) FROM `homes` WHERE `properties`.`id` = `homes`.`property_id` AND `listing_id` IS NOT NULL AND NOT EXISTS (SELECT * FROM `users` INNER JOIN `tenancies` ON `users`.`id` = `tenancies`.`user_id` WHERE `homes`.`id` = `tenancies`.`home_id` AND (`ended_at` IS NULL OR `ended_at` > ?)) AND ((`unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR (`unavailable_before_monthly` IS NOT NULL AND `unavailable_before_monthly` <= ?)) AND ((`unavailable_after_nightly` IS NULL AND `unavailable_before_nightly` IS NULL AND `unavailable_after_monthly` IS NULL AND `unavailable_before_monthly` IS NULL) OR NOT (`unavailable_after_nightly` <= ? AND `unavailable_before_nightly` IS NULL) OR NOT (`unavailable_after_monthly` <= ? AND `unavailable_before_monthly` IS NULL)) AND `active_when_unavailable` = ? AND EXISTS (SELECT * FROM `properties` WHERE `homes`.`property_id` = `properties`.`id` AND `properties`.`offboarded` = ? AND `properties`.`active` = ? AND `properties`.`offboarded` = ? AND `properties`.`active` = ?) AND `homes`.`active` = ?) AS `vacant_homes_count_sort` FROM `properties` LEFT JOIN `listings` ON `listings`.`property_id` = `properties`.`id` INNER JOIN `buildings` AS `b` ON `b`.`id` = `properties`.`building_id` INNER JOIN `areas` AS `a` ON `a`.`id` = `b`.`area_id` INNER JOIN `regions` AS `r` ON `r`.`id` = `a`.`region_id` INNER JOIN `listings` AS `l` ON `l`.`property_id` = `properties`.`id` INNER JOIN `homes` AS `h` ON `h`.`listing_id` = `l`.`id` WHERE `r`.`id` IN (?, ?, ?) AND `l`.`monthly_base_price` IS NOT NULL AND `h`.`max_adults` IN (?, ?, ?, ?, ?) AND `properties`.`offboarded` = ? AND `properties`.`active` = ? GROUP BY `properties`.`id` ORDER BY `properties`.`priority` DESC, ( IF ( ( SELECT COUNT(homes.id) FROM homes INNER JOIN listings ON listings.id = homes.listing_id WHERE properties.id = listings.property_id AND NOT EXISTS ( SELECT * FROM users INNER JOIN tenancies ON users.id = tenancies.user_id WHERE homes.id = tenancies.home_id AND (ended_at IS NULL OR `ended_at` > '2024-06-03') ) AND ( (unavailable_after_monthly IS NULL AND unavailable_before_monthly IS NULL) OR ( unavailable_before_monthly IS NOT NULL AND unavailable_before_monthly <= '2024-06-05' ) ) AND active_when_unavailable = 1 AND active = 1 ), 0, IF ( -( SELECT listings.available_date_monthly FROM listings WHERE properties.id = listings.property_id ORDER BY -(listings.available_date_monthly) DESC LIMIT 1 ), 1, 2 ) ) ) ASC, -( SELECT IF ( available_now_monthly = 1, DATE(NOW()), available_date_monthly ) AS min_available_date FROM listings WHERE properties.id = listings.property_id ORDER BY -(min_available_date) DESC LIMIT 1 ) DESC, ( EXISTS( SELECT listings.id FROM listings JOIN listing_promotion ON listing_id = listings.id WHERE listings.property_id = properties.id ) ) DESC, EXISTS ( SELECT listings.id FROM listings WHERE listings.property_id = properties.id AND ( listings.absolute_discount_1m IS NOT NULL OR listings.percent_discount_1m IS NOT NULL OR listings.absolute_discount_3m IS NOT NULL OR listings.percent_discount_3m IS NOT NULL OR listings.absolute_discount_6m IS NOT NULL OR listings.percent_discount_6m IS NOT NULL OR listings.absolute_discount_12m IS NOT NULL OR listings.percent_discount_12m IS NOT NULL ) ) DESC, -( IF( cheapest_monthly_available_listing_monthly_price_for_sorting IS NOT NULL, cheapest_monthly_available_listing_monthly_price_for_sorting, cheapest_monthly_listing_monthly_price_for_sorting ) ) DESC, `vacant_homes_count_sort` DESC, `properties`.`created_at` DESC LIMIT 20 OFFSET 0

The Pulse dashboard becomes almost unusable.

jessarcher commented 5 months ago

Hey @chimit,

Check out the docs for instructions on how to disable syntax highlighting on SQL queries: https://laravel.com/docs/pulse#slow-queries-card.

chimit commented 5 months ago

Oh, sorry, missed that in the docs. Thank you!