Slow query - please help
am 30.09.2011 17:49:33 von Tompkins Neil
--000e0cd1d13e4d53cf04ae2a92e0
Content-Type: text/plain; charset=ISO-8859-1
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
--000e0cd1d13e4d53cf04ae2a92e0--
Re: Slow query - please help
am 30.09.2011 21:23:47 von Tompkins Neil
--20cf307c9f3880813c04ae2d9048
Content-Type: text/plain; charset=ISO-8859-1
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 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@ipi.co.za/ (secondary:mcarson@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.
>
>
>
--20cf307c9f3880813c04ae2d9048--
Fwd: Slow query - please help
am 04.10.2011 21:45:46 von Tompkins Neil
--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@ipi.co.za/ (secondary:mcarson@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
>=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--
Re: Slow query - please help
am 05.10.2011 04:12:02 von Johnny Withers
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins wro=
te:
> 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 ar=
e 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 re=
sponse
>>=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_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
>>>=20
>>> 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.=
>>>=20
>>> Thanks
>>> Neil
>>>=20
>>=20
>> --
>> Mark Carson
>> Managing
>> Integrated Product Intelligence CC (CK95/35630/23)
>> EMail : mcarson@ipi.co.za/ (secondary:mcarson@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
>>=20
>>=20
>> This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intend=
ed
>> only for use of the addressee. If you are not the addressee, or the perso=
n
>> responsible for delivering it to the person addressed, you may not copy o=
r
>> deliver this to anyone else. If you received this e-mail by mistake, plea=
se
>> do not make use of it, nor disclose it's contents to anyone. Thank you fo=
r
>> notifying us immediately by return e-mail or telephone. INFORMATION PROVI=
DED
>> IN THIS ELECTRONIC MAIL IS PROVIDED "AS IS" WITHOUT WARRANTY REPRESENTATI=
ON
>> OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT
>> LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FO=
R A
>> PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY A=
ND
>> THE USE OF THIS DOCUMENT.
>>=20
>>=20
>>=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: Slow query - please help
am 05.10.2011 10:41:45 von Tompkins Neil
--bcaec54eebfa9e968704ae892ddf
Content-Type: text/plain; charset=ISO-8859-1
Hi
Here is the response :
'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', 'ALL', 'IDX_language_code', NULL,
NULL, NULL, '1207', '99.75', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ALL', 'IDX_language_code', NULL, NULL,
NULL, '163102', '100.00', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_enabled,IDX_language_code', 'IDX_enabled', '1', 'const',
'267', '100.00', 'Using index condition; Using where'
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers 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
> wrote:
>
> > 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
> >>
> >
> >> 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
> 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@ipi.co.za/ (secondary:mcarson@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.
> >>
> >>
> >>
>
--bcaec54eebfa9e968704ae892ddf--
Re: Slow query - please help
am 05.10.2011 10:48:45 von Tompkins Neil
--20cf307ca576a7cc6504ae8946a8
Content-Type: text/plain; charset=ISO-8859-1
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_co de',
'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 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
> wrote:
>
> > 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
> >>
> >
> >> 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
> 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@ipi.co.za/ (secondary:mcarson@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.
> >>
> >>
> >>
>
--20cf307ca576a7cc6504ae8946a8--
Fwd: Slow query - please help
am 05.10.2011 11:01:53 von Tompkins Neil
--20cf307ca5769cf31c04ae897523
Content-Type: text/plain; charset=ISO-8859-1
Following my mail below, if anyone can help optimise the query further that
would be a great help.
---------- 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@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_co de',
'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 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
> wrote:
>
> > 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
> >>
> >
> >> 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
> 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@ipi.co.za/ (secondary:mcarson@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.
> >>
> >>
> >>
>
--20cf307ca5769cf31c04ae897523--
Re: Slow query - please help
am 05.10.2011 14:34:18 von Johnny Withers
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@lists.mysql.com"
>=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_co de',
> '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@ipi.co.za/ (secondary:mcarson@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
>>>>=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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: Slow query - please help
am 05.10.2011 15:26:59 von Tompkins Neil
--20cf307c9f38a9c59504ae8d2914
Content-Type: text/plain; charset=ISO-8859-1
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 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
> wrote:
>
> > Following my mail below, if anyone can help optimise the query further
> that
> > would be a great help.
> >
> > ---------- 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@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_co de',
> > '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
> 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
> >
> >> wrote:
> >>
> >>> 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
> >>>>
> >>>
> >>>> 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
> >> 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@ipi.co.za/ (secondary:mcarson@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.
> >>>>
> >>>>
> >>>>
> >>
>
--20cf307c9f38a9c59504ae8d2914--