Exists clauses in where is taking 60+ seconds to run

[cs.punk]

I have a query thats taking like 60 seconds to run, instead of the usual 0.xx seconds. It is due to my multiple 'exist' clauses on the establishment_attribute table, but why are they taking so long?

 

Any ideas?

 

Here is the query:

select * from accommodation inner join address on accommodation.address_id=address.address_id inner join country on address.country_id = country.country_id inner join establishments on accommodation.accommodation_id = establishments.child_id inner join person on establishments.person_id = person.person_id
WHERE ( EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 11) OR EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 13) OR EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 12) ) AND ( EXISTS( SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 139) )

Table structure:

CREATE TABLE IF NOT EXISTS `accommodation` (
  `accommodation_id` int(16) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `heading` varchar(150) NOT NULL DEFAULT '',
  `description` text,
  `website` tinytext,
  `active` enum('true','false') NOT NULL DEFAULT 'true',
  `finalised` tinyint(1) NOT NULL,
  `directions` text,
  `comments` text,
  `currency_id` int( NOT NULL DEFAULT '0',
  `price_minimum` varchar(255) DEFAULT NULL,
  `price_maximum` varchar(255) DEFAULT NULL,
  `pricing_structure` text,
  `special_deals` text,
  `check_in` time DEFAULT NULL,
  `check_out` time DEFAULT NULL,
  `insert_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activated` enum('true','false') NOT NULL DEFAULT 'true',
  `address_id` int(16) NOT NULL,
  `pageviews` int(16) NOT NULL DEFAULT '0',
  `bbid` varchar(56) DEFAULT NULL,
  `lat` float NOT NULL,
  `lng` float NOT NULL,
  `note` text,
  PRIMARY KEY (`accommodation_id`),
  FULLTEXT KEY `name` (`name`),
  FULLTEXT KEY `heading` (`heading`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `name_2` (`name`,`heading`,`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `address` (
  `address_id` int(16) NOT NULL AUTO_INCREMENT,
  `address1` varchar(255) DEFAULT NULL,
  `address2` varchar(255) DEFAULT NULL,
  `address3` varchar(255) DEFAULT NULL,
  `postal_code` varchar(64) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `city` varchar(128) DEFAULT NULL,
  `country_id` int(16) DEFAULT NULL,
  `district` varchar(128) NOT NULL,
  `suburb` varchar(128) NOT NULL,
  `establishment_type_id` int(16) NOT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3895 ;

CREATE TABLE IF NOT EXISTS `establishments` (
  `establishment_id` int(16) NOT NULL AUTO_INCREMENT,
  `child_id` int(16) NOT NULL,
  `establishment_type_id` int(16) NOT NULL,
  `person_id` int(16) NOT NULL,
  PRIMARY KEY (`establishment_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=856 ;


CREATE TABLE IF NOT EXISTS `establishment_attribute` (
  `establishment_attribute_id` int(16) NOT NULL AUTO_INCREMENT,
  `establishment_id` int(16) NOT NULL DEFAULT '0',
  `attribute_id` int(16) NOT NULL DEFAULT '0',
  PRIMARY KEY (`establishment_attribute_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=180559 ;

[cs.punk]

Here the query explained:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	establishments 	ALL 	NULL	NULL	NULL	NULL	906 	
1 	PRIMARY 	accommodation 	eq_ref 	PRIMARY 	PRIMARY 	4 	glm.establishments.child_id 	1 	Using where
1 	PRIMARY 	person 	eq_ref 	PRIMARY 	PRIMARY 	4 	glm.establishments.person_id 	1 	
1 	PRIMARY 	address 	eq_ref 	PRIMARY 	PRIMARY 	4 	glm.accommodation.address_id 	1 	
1 	PRIMARY 	country 	eq_ref 	PRIMARY 	PRIMARY 	4 	glm.address.country_id 	1 	
5 	DEPENDENT SUBQUERY 	establishment_attribute 	ALL 	NULL	NULL	NULL	NULL	40862 	Using where
4 	DEPENDENT SUBQUERY 	establishment_attribute 	ALL 	NULL	NULL	NULL	NULL	40862 	Using where
3 	DEPENDENT SUBQUERY 	establishment_attribute 	ALL 	NULL	NULL	NULL	NULL	40862 	Using where
2 	DEPENDENT SUBQUERY 	establishment_attribute 	ALL 	NULL	NULL	NULL	NULL	40862 	Using where

[mikosiko]

All the dependant subqueries can be replaced with an addtional JOIN and using IN for the attribute_id, however why are you joining the table establishment_attribute with the table accomodations when seems that the right relationship is with the table establishment using the establishment_id field, moreover when you are joining the table establishments with accomodations using the field establishments.child_id

Edited April 11, 2013 by mikosiko

[cs.punk]

All the dependant subqueries can be replaced with an addtional JOIN and using IN for the attribute_id, however why are you joining the table establishment_attribute with the table accomodations when seems that the right relationship is with the table establishment using the establishment_id field, moreover when you are joining the table establishments with accomodations using the field establishments.child_id

 

I don't think I understand your question.

 

My initial dilemma with using JOINS instead of the subqueries however was, there are multiple rows in establishment_attribute with a foreign table id that points back to the accommodation table. In other words, a one to many relationship.

[DavidAM]

Since establishment_id in establishment_attribute is a Foreign Key to establishments, you should define an index on it.

 

CREATE TABLE IF NOT EXISTS `establishment_attribute` (
  `establishment_attribute_id` int(16) NOT NULL AUTO_INCREMENT,
  `establishment_id` int(16) NOT NULL DEFAULT '0',
  `attribute_id` int(16) NOT NULL DEFAULT '0',
  PRIMARY KEY (`establishment_attribute_id`),
  INDEX(establishment_id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=180559 ;

You should probably do this with all of your other foreign key columns as well. If you define the foreign key constraint in the CREATE TABLE statement, it has been my experience that mySql will create the index (automatically) even if the engine does not support foreign keys.

 

CREATE TABLE IF NOT EXISTS `establishment_attribute` (
  `establishment_attribute_id` int(16) NOT NULL AUTO_INCREMENT,
  `establishment_id` int(16) NOT NULL DEFAULT '0',
  `attribute_id` int(16) NOT NULL DEFAULT '0',
  PRIMARY KEY (`establishment_attribute_id`),
  FOREIGN KEY (establishment_id) REFERENCES establishments(establishment_id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=180559 ;

[Psycho]

First, you would do yourself, and others, a huge favor by formatting your queries in your code for readability. A little extra whitespace characters would have no performance degredation but you will gain much in the way of debugging your code.

 

SELECT *
FROM accommodation
INNER JOIN address
  ON accommodation.address_id = address.address_id
INNER JOIN country
  ON address.country_id = country.country_id
INNER JOIN establishments
  ON accommodation.accommodation_id = establishments.child_id
INNER JOIN person
  ON establishments.person_id = person.person_id
LEFT JOIN establishment_attribute AS attr1
  ON attr1.establishment_id = accommodation.accommodation_id AND

WHERE
(
    EXISTS (
        SELECT 1 FROM establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 11
    )
    OR EXISTS (
        SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 13
    )
    OR EXISTS (
        SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 12
    )
)
AND
(
    EXISTS
    (
         SELECT 1 from establishment_attribute WHERE establishment_id = accommodation.accommodation_id AND attribute_id = 139
    )
)

 

Second, those first three EXISTS subqueries could be replaced with 1 queries using an IN condition for the three attribute_id values

 

AND attribute_id IN (11, 12, 13)

 

 

Third, Don't use '*' in your SELECT statement if you don't really need all the data. I highly doubt you need every field from all those joined tables.

 

 

Fourth,

 

My initial dilemma with using JOINS instead of the subqueries however was, there are multiple rows in establishment_attribute with a foreign table id that points back to the accommodation table. In other words, a one to many relationship.

 

You can still do a JOIN and also a GROUP BY to prevent duplicates. Running subqueries when not needed is a bad idea. I like to have test data when constructing involved queries, so I can't verify this is what you need, but I think so

 

SELECT *
FROM accommodation
INNER JOIN address
  ON accommodation.address_id = address.address_id
INNER JOIN country
  ON address.country_id = country.country_id
INNER JOIN establishments
  ON accommodation.accommodation_id = establishments.child_id
INNER JOIN person
  ON establishments.person_id = person.person_id
LEFT JOIN establishment_attribute AS attr1
  ON attr1.establishment_id = accommodation.accommodation_id AND attr1.attribute_id IN (11, 12, 13)
LEFT JOIN establishment_attribute AS attr2
  ON attr2.establishment_id = accommodation.accommodation_id AND attr1.attribute_id = 139

GROUP BY attr1.establishment_id, attr2.establishment_id

WHERE attr1.establishment_id NOT NULL
  AND attr2.establishment_id NOT NULL