From: Neil Tompkins Date: October 4 2011 7:45pm Subject: Fwd: Slow query - please help List-Archive: http://lists.mysql.com/mysql/225916 Message-Id: MIME-Version: 1.0 (iPhone Mail 8L1) Content-Type: multipart/alternative; boundary=Apple-Mail-1-147932386 Content-Transfer-Encoding: 7bit --Apple-Mail-1-147932386 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Can anyone help me ? Begin forwarded message: > 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 > 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 res= ponse >=20 > Mark >=20 > 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 =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_code= =3D > > 'en') AS available_hotel_count, > > (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id =3D= > > cities.city_id AND districts.language_code =3D 'en' AND districts.countr= y_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 > > > > Previously the table format was Innodb with foreign keys and the query w= as > > pretty much instant. Now I've changed the table format to MyISAM and > > obviously removed the foreign keys and the query takes forever to execut= e > > using the same data. Can anyone help and tell me where I've gone wrong.= > > > > Thanks > > Neil > > >=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 intende= d > 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, pleas= e > 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 PROVID= ED > IN THIS ELECTRONIC MAIL IS PROVIDED "AS IS" WITHOUT WARRANTY REPRESENTATIO= N > 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 AN= D > THE USE OF THIS DOCUMENT. >=20 >=20 >=20 --Apple-Mail-1-147932386--