List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:September 30 2011 7:23pm
Subject:Re: Slow query - please help
View as plain text  
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 <mcarson@stripped> 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 : 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
>
>
> 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.
>
>
>

Thread
Slow query - please helpTompkins Neil30 Sep
Re: Slow query - please helpTompkins Neil30 Sep
  • Fwd: Slow query - please helpNeil Tompkins4 Oct
    • Re: Slow query - please helpJohnny Withers5 Oct
      • Re: Slow query - please helpTompkins Neil5 Oct
      • Re: Slow query - please helpTompkins Neil5 Oct
        • Fwd: Slow query - please helpTompkins Neil5 Oct
          • Re: Slow query - please helpJohnny Withers5 Oct
            • Re: Slow query - please helpTompkins Neil5 Oct