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