I already have a FULLTEXT index on cities.name ? Do I still need to add a
normal index ?
On Wed, Oct 5, 2011 at 1:34 PM, Johnny Withers <joh...@pixelated.net> wrote:
> 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.
> >>>>
> >>>>
> >>>>
> >>
>
