date:20111005

Re: Slow query - please help

2011-10-05 Thread Tompkins Neil

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 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

Re: Slow query - please help

2011-10-05 Thread Tompkins Neil

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,

Fwd: Slow query - please help

2011-10-05 Thread Tompkins Neil

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

Re: How MyISAM handle auto_increment

2011-10-05 Thread Lucio Chiappetti


On Mon, 3 Oct 2011, Reindl Harald wrote:


I have questions regarding how MyISAM handles auto_increment clolumn?



it is a table-property and you hould NOT touch it without godd reasons
because it is named AUTO


I guess there are quite often good reasons to change it, which can be
done e.g. as

alter table north33b  auto_increment=21;

I often generate an empty table with the same structure as another table 
with a statement like :


create table  select * from  limit 0;

(at this point I have to recreate also all indices etc. doing a show 
create table  and show create table  and alter table  for 
anything which is missing)


The new table  will have auto increment starting where  ended. 
This is good (assuming  will not grow any more) to preserve an 
UNIQUE sequence for all tables different for each table.


In case one wants  to start at a round number one can just issue an 
alter table and reset auto_increment.


The same if one tests some procedure to populate a new table , then 
deletes everything, and wants that production population restarts from 1.


--

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)

Citizens entrusted of public functions have the duty to accomplish them
with discipline and honour
  [Art. 54 Constitution of the Italian Republic]

For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Slow query - please help

2011-10-05 Thread Johnny Withers

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,

Re: Slow query - please help

2011-10-05 Thread Tompkins Neil

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,

Re: Slow query - please help

Re: Slow query - please help

Fwd: Slow query - please help

Re: How MyISAM handle auto_increment

Re: Slow query - please help

Re: Slow query - please help

6 matches

Site Navigation

Mail list logo

Footer information