UNION query going slow
Posted: Tue May 29, 2012 3:33 am
I recently was trying to optimize a query and got some great help with UNIONs here: viewtopic.php?f=2&t=135360
Now I have been going on with this, adding more "abilities" to the query but this has gotten me into a jam. While all of the individual queries run supra fast (5 queries, total time if run individually = 0.0275 seconds) but as soon as I UNION them, the speed dies, rising to 2.7843 seconds. This is sad for me because using UNION on this stuff would really help me out but if this query can't be run at a decent speed I will have to go drastic and murder the unions and go with PHP to sort through the muck.
I read that if I use LIMIT or ORDER BY then some things will slow down a lot but I don't see a way around using these since I am paginating the results and I need them to be returned according their "score" on how well their name matches to the word. My final query that I am using right now looks like this:
The output of "explain" is attached in the image attachment on this post.
Can anyone shed some light on why this is going to slow? The ~3 seconds on my local host turns into ~5 seconds on the live server (shared host) so it is becoming quite a problem since this is our main search engine.
Now I have been going on with this, adding more "abilities" to the query but this has gotten me into a jam. While all of the individual queries run supra fast (5 queries, total time if run individually = 0.0275 seconds) but as soon as I UNION them, the speed dies, rising to 2.7843 seconds. This is sad for me because using UNION on this stuff would really help me out but if this query can't be run at a decent speed I will have to go drastic and murder the unions and go with PHP to sort through the muck.
I read that if I use LIMIT or ORDER BY then some things will slow down a lot but I don't see a way around using these since I am paginating the results and I need them to be returned according their "score" on how well their name matches to the word. My final query that I am using right now looks like this:
Code: Select all
(
SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('bygg') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
LEFT JOIN `nr_company_addresses` AS `companyAddresses` ON companies.companyid = companyAddresses.companyid AND companyAddresses.primary = "1"
LEFT JOIN `nr_cities` AS `cities` ON companyAddresses.cityid = cities.cityid WHERE (MATCH(companies.name) AGAINST ('bygg')) AND (companies.active = "1")
)
UNION
(
SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('bygg') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
INNER JOIN `nr_company_keywords` AS `companyKeywords` ON companyKeywords.companyid = companies.companyid
INNER JOIN `nr_keywords` AS `keywords` ON keywords.keywordid = companyKeywords.keywordid
LEFT JOIN `nr_company_addresses` AS `companyAddresses` ON companies.companyid = companyAddresses.companyid AND companyAddresses.primary = "1"
LEFT JOIN `nr_cities` AS `cities` ON companyAddresses.cityid = cities.cityid WHERE (MATCH(keywords.word) AGAINST ('bygg')) AND (companies.active = "1")
)
UNION
(
SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('bygg') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
LEFT JOIN `nr_company_addresses` AS `companyAddresses` ON companies.companyid = companyAddresses.companyid AND companyAddresses.primary = "1"
LEFT JOIN `nr_cities` AS `cities` ON companyAddresses.cityid = cities.cityid WHERE (MATCH(childCategories.name) AGAINST ('bygg')) AND (companies.active = "1")
)
UNION
(
SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('bygg') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
INNER JOIN `nr_company_addresses` AS `companyAddresses` ON companyAddresses.companyid = companies.companyid AND companyAddresses.primary = "1"
LEFT JOIN `nr_cities` AS `cities` ON companyAddresses.cityid = cities.cityid WHERE (companies.phoneNumber = 'bygg') AND (companies.active = "1")
)
UNION
(
SELECT `companies`.`companyid`, `companies`.`childCategoryid`, `companies`.`registrationNumber`, `companies`.`name`, `companies`.`phoneNumber`, `companies`.`member`, MATCH(companies.name) AGAINST ('bygg') AS `score`, `childCategories`.`name` AS `childCategory`, `companyAddresses`.`address`, `cities`.`name` AS `city` FROM `nr_companies` AS `companies`
INNER JOIN `nr_child_categories` AS `childCategories` ON childCategories.childCategoryid = companies.childCategoryid
INNER JOIN `nr_child_category_keywords` AS `childCategoryKeywords` ON childCategories.childCategoryid = childCategoryKeywords.childCategoryid
INNER JOIN `nr_keywords` AS `keywords` ON keywords.keywordid = childCategoryKeywords.keywordid
LEFT JOIN `nr_company_addresses` AS `companyAddresses` ON companyAddresses.companyid = companies.companyid AND companyAddresses.primary = "1"
LEFT JOIN `nr_cities` AS `cities` ON companyAddresses.cityid = cities.cityid WHERE (MATCH(keywords.word) AGAINST ('bygg')) AND (companies.active = "1")
)
ORDER BY `member` DESC, `score` DESC, `name` ASC LIMIT 20Can anyone shed some light on why this is going to slow? The ~3 seconds on my local host turns into ~5 seconds on the live server (shared host) so it is becoming quite a problem since this is our main search engine.