Hi Here is the response :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition; Using where; Using filesort' '4', 'DEPENDENT SUBQUERY', 'districts', 'ALL', 'IDX_language_code', NULL, NULL, NULL, '1207', '99.75', 'Using where' '3', 'DEPENDENT SUBQUERY', 'hotels', 'ALL', 'IDX_language_code', NULL, NULL, NULL, '163102', '100.00', 'Using where' '2', 'DEPENDENT SUBQUERY', 'hotels', 'ref', 'UNQ_folder_url,IDX_enabled,IDX_language_code', 'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using where' Thanks Neil On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers <joh...@pixelated.net> wrote: > Can you post the explain extended output of your query? > > Sent from my iPad > > On Oct 4, 2011, at 2:45 PM, Neil Tompkins <neil.tompk...@googlemail.com> > wrote: > > > Can anyone help me ? > > > > > > Begin forwarded message: > > > >> From: Tompkins Neil <neil.tompk...@googlemail.com> > >> Date: 30 September 2011 20:23:47 GMT+01:00 > >> To: mark carson <mcar...@pixie.co.za> > >> Cc: "[MySQL]" <mysql@lists.mysql.com> > >> Subject: Re: Slow query - please help > >> > > > >> I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions > are below, let me know if you need any more information. > >> > >> CREATE TABLE `districts` ( > >> `district_id` int(11) NOT NULL, > >> `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en', > >> `city_id` int(11) DEFAULT NULL, > >> `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, > >> `latitude` double DEFAULT NULL, > >> `longitude` double DEFAULT NULL, > >> `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', > >> `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > >> PRIMARY KEY (`district_id`,`language_code`), > >> UNIQUE KEY `UNQ_folder_url` (`folder_url`), > >> KEY `IDX_country_code` (`country_code`), > >> KEY `IDX_enabled` (`enabled`), > >> KEY `IDX_folder_url` (`folder_url`), > >> KEY `IDX_language_code` (`language_code`), > >> KEY `IDX_latitude` (`latitude`), > >> KEY `IDX_longitude` (`longitude`) > >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >> > >> CREATE TABLE `cities` ( > >> `city_id` int(11) NOT NULL, > >> `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL, > >> `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, > >> `nr_hotels` int(11) DEFAULT NULL, > >> `latitude` double DEFAULT NULL, > >> `longitude` double DEFAULT NULL, > >> `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', > >> `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > >> PRIMARY KEY (`city_id`,`language_code`), > >> UNIQUE KEY `UNQ_folder_url` (`folder_url`), > >> KEY `IDX_country_code` (`country_code`), > >> KEY `IDX_enabled` (`enabled`), > >> KEY `IDX_folder_url` (`folder_url`), > >> KEY `IDX_language_code` (`language_code`), > >> KEY `IDX_latitude` (`latitude`), > >> KEY `IDX_longitude` (`longitude`) > >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >> > >> > >> CREATE TABLE `hotels` ( > >> `hotel_id` int(11) NOT NULL, > >> `language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en', > >> `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `city_id` int(11) DEFAULT NULL, > >> `class_is_estimated` tinyint(4) DEFAULT NULL, > >> `class` tinyint(4) DEFAULT NULL, > >> `country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `hoteltype_id` int(11) DEFAULT NULL, > >> `is_closed` tinyint(4) DEFAULT NULL, > >> `latitude` double DEFAULT NULL, > >> `longitude` double DEFAULT NULL, > >> `maxrate` double DEFAULT NULL, > >> `minrate` double DEFAULT NULL, > >> `original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `nr_rooms` int(11) DEFAULT NULL, > >> `preferred` int(11) DEFAULT NULL, > >> `ranking` int(11) DEFAULT NULL, > >> `review_nr` int(11) DEFAULT NULL, > >> `review_score` double DEFAULT NULL, > >> `zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `checkin_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `checkout_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `checkout_to` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `folder_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `short_profile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, > >> `enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N', > >> `last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, > >> PRIMARY KEY (`hotel_id`,`language_code`), > >> UNIQUE KEY `UNQ_folder_url` (`folder_url`), > >> KEY `IDX_country_code` (`country_code`), > >> KEY `IDX_enabled` (`enabled`), > >> KEY `IDX_language_code` (`language_code`), > >> KEY `IDX_latitude` (`latitude`), > >> KEY `IDX_longitude` (`longitude`), > >> KEY `IDX_name` (`name`) > >> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; > >> > >> On Fri, Sep 30, 2011 at 6:08 PM, mark carson <mcar...@pixie.co.za> > wrote: > >> Hi > >> > >> You need version of mysql and table/key/index layout in order to get a > response > >> > >> Mark > >> > >> On 2011/09/30 17:49, Tompkins Neil wrote: > >>> 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 > >>> > >> > >> -- > >> Mark Carson > >> Managing > >> Integrated Product Intelligence CC (CK95/35630/23) > >> EMail : mcar...@ipi.co.za/ (secondary:mcar...@pixie.co.za) > >> Physical Address : 34 Spanish Galliard, Mooikloof, Pretoria, South > Africa > >> snailmail : P.O. Box 36095 Menlo Park 0102, South Africa > >> Tel. +27 12 996 1193/1815 Fax : +27 86 672 7012 Cell : +27 83 260 8515 > >> > >> > >> This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION > intended > >> only for use of the addressee. If you are not the addressee, or the > person > >> responsible for delivering it to the person addressed, you may not copy > or > >> deliver this to anyone else. If you received this e-mail by mistake, > please > >> do not make use of it, nor disclose it's contents to anyone. Thank you > for > >> notifying us immediately by return e-mail or telephone. INFORMATION > PROVIDED > >> IN THIS ELECTRONIC MAIL IS PROVIDED "AS IS" WITHOUT WARRANTY > REPRESENTATION > >> OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT > >> LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS > FOR A > >> PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY > AND > >> THE USE OF THIS DOCUMENT. > >> > >> > >> >