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.
>>
>>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
