List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:September 30 2011 3:49pm
Subject:Slow query - please help
View as plain text  
Hi

I've the following query :

SELECT city_id, name, meta_title, meta_description, meta_keywords,
country_code, link_text, folder_url, enabled, last_changed, nr_hotels,
(SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id =
cities.city_id AND hotels.country_code = 'gb' AND hotels.enabled = 'Y' AND
hotels.folder_url IS NOT NULL AND hotels.language_code = 'en') AS
hotel_count,
(SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id =
cities.city_id AND hotels.country_code = 'gb' AND hotels.language_code =
'en') AS available_hotel_count,
(SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id =
cities.city_id AND districts.language_code = 'en' AND districts.country_code
= 'gb') AS district_count
FROM cities WHERE language_code = 'en' AND country_code = 'gb'
ORDER BY cities.name ASC , cities.city_id ASC

Previously the table format was Innodb with foreign keys and the query was
pretty much instant.  Now I've changed the table format to MyISAM and
obviously removed the foreign keys and the query takes forever to execute
using the same data.  Can anyone help and tell me where I've gone wrong.

Thanks
Neil

Thread
Slow query - please helpTompkins Neil30 Sep
Re: Slow query - please helpTompkins Neil30 Sep
  • Fwd: Slow query - please helpNeil Tompkins4 Oct
    • Re: Slow query - please helpJohnny Withers5 Oct
      • Re: Slow query - please helpTompkins Neil5 Oct
      • Re: Slow query - please helpTompkins Neil5 Oct
        • Fwd: Slow query - please helpTompkins Neil5 Oct
          • Re: Slow query - please helpJohnny Withers5 Oct
            • Re: Slow query - please helpTompkins Neil5 Oct