Try adding an index on cities.name, it may prevent the file sort. What was the
original query time and what is it now?
Sent from my iPad
On Oct 5, 2011, at 4:01 AM, Tompkins Neil <neil.tompk...@googlemail.com> wrote:
> Following my mail below, if anyone can help optimise the query further that
> would be a great help.
>
> ---------- Forwarded message ----------
> From: Tompkins Neil <neil.tompk...@googlemail.com>
> Date: Wed, Oct 5, 2011 at 9:48 AM
> Subject: Re: Slow query - please help
> To: Johnny Withers <joh...@pixelated.net>
> Cc: "mysql@lists.mysql.com" <mysql@lists.mysql.com>
>
>
> I just revised my query and now get the following output :
>
> '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', 'ref',
> 'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
> '121', '100.00', 'Using index condition; Using where'
> '3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
> 'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
> '9982', '100.00', 'Using index condition; Using where'
> '2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
> 'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
> 'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
> where'
>
> After doing this the query speed is acceptable.
>
> 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.
>>>>
>>>>
>>>>
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
