select query runs slowly 1

[brownfox]

An inner join query on 6 tables none of which hold more than 4000 rows keeps timing out. Here are the tables:

CREATE TABLE `codetbl_language` (
  `code_language` bigint(20) NOT NULL auto_increment,
  `code_abbreviation` varchar(10) default NULL,
  `code_description` varchar(50) default NULL,
  `code_entered` datetime default NULL,
  `code_updated` datetime default NULL,
  `code_user` varchar(50) default NULL,
  PRIMARY KEY  (`code_language`)
) TYPE=MyISAM AUTO_INCREMENT=44 ;

CREATE TABLE `codetbl_membership` (
  `code_membership` varchar(5) default NULL,
  `code_description` varchar(50) default NULL,
  `code_cost` double default NULL,
  `code_classification` char(1) default NULL,
  `code_print` tinyint(4) default NULL,
  `code_entered` datetime default NULL,
  `code_updated` datetime default NULL,
  `code_user` varchar(50) default NULL,
  `code_unique` smallint(4) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`code_unique`)
) TYPE=MyISAM AUTO_INCREMENT=44 ;

CREATE TABLE `codetbl_touristbrd` (
  `code_touristbrd` smallint(4) NOT NULL auto_increment,
  `code_abbreviation` varchar(10) default NULL,
  `code_name` varchar(50) default NULL,
  `code_classification` char(1) default NULL,
  `code_entered` datetime default NULL,
  `code_updated` datetime default NULL,
  `code_user` varchar(50) default NULL,
  PRIMARY KEY  (`code_touristbrd`)
) TYPE=MyISAM AUTO_INCREMENT=17 ;

CREATE TABLE `tbl_guides` (
  `guide_code` smallint(4) unsigned NOT NULL auto_increment,
  `guide_surname` varchar(50) default NULL,
  `guide_forename` varchar(50) default NULL,
  `guide_midname` varchar(50) default NULL,
  `guide_title` varchar(50) default NULL,
  `guide_gender` char(1) default NULL,
  `guide_dob` datetime default NULL,
  `guide_qualifications` varchar(50) default NULL,
  `guide_print` tinyint(4) default '-1',
  `guide_membership` varchar(50) default NULL,
  `guide_street` varchar(50) default NULL,
  `guide_locality` varchar(50) default NULL,
  `guide_town` varchar(50) default NULL,
  `guide_county` varchar(50) default NULL,
  `guide_postcode` varchar(10) default NULL,
  `guide_addressonwebsite` tinyint(4) NOT NULL default '-1',
  `guide_email` varchar(254) default NULL,
  `guide_internet` varchar(254) default NULL,
  `guide_internetlisting` tinyint(4) default NULL,
  `guide_emailnewsletter` tinyint(4) default NULL,
  `guide_biopic` varchar(254) default NULL,
  `guide_memo` longtext,
  `guide_aptg` tinyint(4) default NULL,
  `guide_original` bigint(20) default NULL,
  `guide_entered` datetime default NULL,
  `guide_updated` datetime default NULL,
  `guide_user` varchar(50) default NULL,
  `guide_password` varchar(30) default NULL,
  `guide_attempts` tinyint(4) default NULL,
  `guide_lastlogin` datetime default NULL,
  `guide_username` varchar(30) default NULL,
  PRIMARY KEY  (`guide_code`)
) TYPE=MyISAM AUTO_INCREMENT=3285 ;

CREATE TABLE `tbl_languages` (
  `language_guide` bigint(20) default NULL,
  `language_code` bigint(20) default NULL,
  `language_mother` tinyint(4) default NULL,
  `language_entered` datetime default NULL,
  `language_updated` datetime default NULL,
  `language_user` varchar(50) default NULL,
  `language_unique` bigint(20) NOT NULL auto_increment,
  PRIMARY KEY  (`language_unique`)
) TYPE=MyISAM AUTO_INCREMENT=2541 ;

CREATE TABLE `tbl_registrations` (
  `registration_guide` smallint(4) unsigned default NULL,
  `registration_touristbrd` bigint(20) default NULL,
  `registration_area` varchar(50) default NULL,
  `registration_type` varchar(50) default NULL,
  `registration_year` double default NULL,
  `registration_order` int(11) default NULL,
  `registration_association` bigint(20) default NULL,
  `registration_entered` datetime default NULL,
  `registration_updated` datetime default NULL,
  `registration_user` varchar(50) default NULL,
  `registration_code` bigint(20) NOT NULL auto_increment,
  `registration_original` bigint(20) default NULL,
  PRIMARY KEY  (`registration_code`)
) TYPE=MyISAM AUTO_INCREMENT=2655 ;

And the query:

SELECT DISTINCT tbl_languages.language_guide,codetbl_language.code_description,tbl_guides.guide_surname,tbl_guides.guide_forename, codetbl_touristbrd.code_abbreviation ".
	"	FROM tbl_registrations	".
	" INNER JOIN tbl_languages ON ".
	" (tbl_languages.language_guide = tbl_registrations.registration_guide) ".
	" INNER JOIN tbl_guides ON ".
	" (tbl_guides.guide_code = tbl_registrations.registration_guide) ".
	" INNER JOIN codetbl_touristbrd ON ".
	" (codetbl_touristbrd.code_touristbrd  = tbl_registrations.registration_touristbrd) ".
	" INNER JOIN codetbl_language ON ".
	" (codetbl_language.code_language  = tbl_languages.language_code) ".
	" INNER JOIN codetbl_membership ON ".
	" (tbl_guides.guide_membership = codetbl_membership.code_membership)".
	" WHERE ((tbl_registrations.registration_type = 'Main')".
	" AND (codetbl_membership.code_print <> 0) ".
	" AND (codetbl_language.code_abbreviation <> 'ENG')) ".
	" 	ORDER BY codetbl_language.code_abbreviation,tbl_guides.guide_surname,tbl_guides.guide_forename LIMIT 0,72;

Why does this query run so slowly? (Even on my LAN so I don't think it's a server issue). TIA...

 

[r937]

i don't see any indexes on join columns

let's take your first join for example --

ON tbl_languages.language_guide = tbl_registrations.registration_guide

both of those columns need an index, otherwise you get a table scan

usually joins involve the primary key of one table with (a column acting as) the foreign key of another, but in this example, the primary keys of both tbl_languages and tbl_registrations tables seem to be playing no role whatsoever

you can also speed up a query by removing the DISTINCT keyword if it isn't really necessary



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[brownfox]

Thanks for your quick response r937. I have removed the DISTINCT word but I need the joins with the tables you mentioned (tbl_languages and tbl_registrations) due to the WHERE clause and the columns selected:
...WHERE ((tbl_registrations.registration_type = 'Main')
AND (codetbl_membership.code_print <> 0)
AND (codetbl_language.code_abbreviation <> 'ENG'))
ORDER BY codetbl_language.code_abbreviation,tbl_guides.guide_surname,tbl_guides.guide_forename LIMIT 0,72;
Any other ideas?

 

[r937]

other ideas? what about the #1 idea i gave you?

declare indexes on the columns used in joins

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[brownfox]

OK sorry aren't they indexed already?

PRIMARY KEY  (`registration_code`)

and

PRIMARY KEY  (`guide_code`)

 

[brownfox]

You are absolutely right! I was confused between PRIMARY KEY and an INDEX thought they were the same. D'oh . Brilliant. Thanks a lot r937

 

[r937]

yes, that was my point, primary keys have a index (because of the way primary keys work) but in your case you weren't even using the primary key in the joins



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)