From: Johnny Withers Date: October 5 2011 12:34pm Subject: Re: Slow query - please help List-Archive: http://lists.mysql.com/mysql/225927 Message-Id: MIME-Version: 1.0 (iPad Mail 8G4) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Try adding an index on cities.name, it may prevent the file sort. What was t= he original query time and what is it now? Sent from my iPad On Oct 5, 2011, at 4:01 AM, Tompkins Neil wro= te: > Following my mail below, if anyone can help optimise the query further tha= t > would be a great help. >=20 > ---------- Forwarded message ---------- > From: Tompkins Neil > Date: Wed, Oct 5, 2011 at 9:48 AM > Subject: Re: Slow query - please help > To: Johnny Withers > Cc: "mysql@stripped" >=20 >=20 > I just revised my query and now get the following output : >=20 > '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; Usin= g > where' >=20 > After doing this the query speed is acceptable. >=20 > Thanks > Neil >=20 > On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers wrot= e: >=20 >> Can you post the explain extended output of your query? >>=20 >> Sent from my iPad >>=20 >> On Oct 4, 2011, at 2:45 PM, Neil Tompkins >> wrote: >>=20 >>> Can anyone help me ? >>>=20 >>>=20 >>> Begin forwarded message: >>>=20 >>>> From: Tompkins Neil >>>> Date: 30 September 2011 20:23:47 GMT+01:00 >>>> To: mark carson >>>> Cc: "[MySQL]" >>>> Subject: Re: Slow query - please help >>>>=20 >>>=20 >>>> 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. >>>>=20 >>>> 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=3DMyISAM DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_unicode_ci; >>>>=20 >>>> 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=3DMyISAM DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_unicode_ci; >>>>=20 >>>>=20 >>>> 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=3DMyISAM DEFAULT CHARSET=3Dutf8 COLLATE=3Dutf8_unicode_ci; >>>>=20 >>>> On Fri, Sep 30, 2011 at 6:08 PM, mark carson >> wrote: >>>> Hi >>>>=20 >>>> You need version of mysql and table/key/index layout in order to get a >> response >>>>=20 >>>> Mark >>>>=20 >>>> On 2011/09/30 17:49, Tompkins Neil wrote: >>>>> Hi >>>>>=20 >>>>> I've the following query : >>>>>=20 >>>>> 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 =3D >>>>> cities.city_id AND hotels.country_code =3D 'gb' AND hotels.enabled =3D= 'Y' >> AND >>>>> hotels.folder_url IS NOT NULL AND hotels.language_code =3D 'en') AS >>>>> hotel_count, >>>>> (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id =3D >>>>> cities.city_id AND hotels.country_code =3D 'gb' AND hotels.language_co= de >> =3D >>>>> 'en') AS available_hotel_count, >>>>> (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_i= d >> =3D >>>>> cities.city_id AND districts.language_code =3D 'en' AND >> districts.country_code >>>>> =3D 'gb') AS district_count >>>>> FROM cities WHERE language_code =3D 'en' AND country_code =3D 'gb' >>>>> ORDER BY cities.name ASC , cities.city_id ASC >>>>>=20 >>>>> 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. >>>>>=20 >>>>> Thanks >>>>> Neil >>>>>=20 >>>>=20 >>>> -- >>>> Mark Carson >>>> Managing >>>> Integrated Product Intelligence CC (CK95/35630/23) >>>> EMail : mcarson@stripped/ (secondary:mcarson@stripped) >>>> 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 >>>>=20 >>>>=20 >>>> 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. >>>>=20 >>>>=20 >>>>=20 >>=20