Page 1 of 1

UNION query going slow

Posted: Tue May 29, 2012 3:33 am
by shiznatix
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:

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

Re: UNION query going slow

Posted: Tue May 29, 2012 4:01 am
by Benjamin
companies is slowing it down incredibly. The tables in the queries don't have unique aliases so I can't tell exactly what query/join/field is doing it, but it's happening twice.

Instead of writing each query with the same alias:

Code: Select all

SELECT `companies`.`companyid` FROM tableName AS companies
Write each query to use it's own alias:

Code: Select all

SELECT c1.companyid FROM tableName AS c1...

UNION

SELECT c2.companyid FROM tableName AS c2...
That will make the explain statement more verbose.

Re: UNION query going slow

Posted: Tue May 29, 2012 4:30 am
by shiznatix
Roger. The new query with unique aliases is:

Code: Select all

(
SELECT `companies_1`.`companyid`, `companies_1`.`childCategoryid`, `companies_1`.`registrationNumber`, `companies_1`.`name`, `companies_1`.`phoneNumber`, `companies_1`.`member`, MATCH(companies_1.name) AGAINST ('bygg') AS `score`, `childCategories_1`.`name` AS `childCategory`, `companyAddresses_1`.`address`, `cities_1`.`name` AS `city`
FROM `nr_companies` AS `companies_1`
 INNER JOIN `nr_child_categories` AS `childCategories_1` ON childCategories_1.childCategoryid = companies_1.childCategoryid
 LEFT JOIN `nr_company_addresses` AS `companyAddresses_1` ON companies_1.companyid = companyAddresses_1.companyid AND companyAddresses_1.primary = "1"
 LEFT JOIN `nr_cities` AS `cities_1` ON companyAddresses_1.cityid = cities_1.cityid WHERE (MATCH(companies_1.name) AGAINST ('bygg')) AND (companies_1.active = "1")
)

UNION

(
 SELECT `companies_2`.`companyid`, `companies_2`.`childCategoryid`, `companies_2`.`registrationNumber`, `companies_2`.`name`, `companies_2`.`phoneNumber`, `companies_2`.`member`, MATCH(companies_2.name) AGAINST ('bygg') AS `score`, `childCategories_2`.`name` AS `childCategory`, `companyAddresses_2`.`address`, `cities_2`.`name` AS `city`
FROM `nr_companies` AS `companies_2`
 INNER JOIN `nr_child_categories` AS `childCategories_2` ON childCategories_2.childCategoryid = companies_2.childCategoryid
 INNER JOIN `nr_company_keywords` AS `companyKeywords_2` ON companyKeywords_2.companyid = companies_2.companyid
 INNER JOIN `nr_keywords` AS `keywords_2` ON keywords_2.keywordid = companyKeywords_2.keywordid
 LEFT JOIN `nr_company_addresses` AS `companyAddresses_2` ON companies_2.companyid = companyAddresses_2.companyid AND companyAddresses_2.primary = "1"
 LEFT JOIN `nr_cities` AS `cities_2` ON companyAddresses_2.cityid = cities_2.cityid WHERE (MATCH(keywords_2.word) AGAINST ('bygg')) AND (companies_2.active = "1") 
)

UNION

(
 SELECT `companies_3`.`companyid`, `companies_3`.`childCategoryid`, `companies_3`.`registrationNumber`, `companies_3`.`name`, `companies_3`.`phoneNumber`, `companies_3`.`member`, MATCH(companies_3.name) AGAINST ('bygg') AS `score`, `childCategories_3`.`name` AS `childCategory`, `companyAddresses_3`.`address`, `cities_3`.`name` AS `city`
FROM `nr_companies` AS `companies_3`
 INNER JOIN `nr_child_categories` AS `childCategories_3` ON childCategories_3.childCategoryid = companies_3.childCategoryid
 LEFT JOIN `nr_company_addresses` AS `companyAddresses_3` ON companies_3.companyid = companyAddresses_3.companyid AND companyAddresses_3.primary = "1"
 LEFT JOIN `nr_cities` AS `cities_3` ON companyAddresses_3.cityid = cities_3.cityid WHERE (MATCH(childCategories_3.name) AGAINST ('bygg')) AND (companies_3.active = "1") 
)

UNION

(
 SELECT `companies_4`.`companyid`, `companies_4`.`childCategoryid`, `companies_4`.`registrationNumber`, `companies_4`.`name`, `companies_4`.`phoneNumber`, `companies_4`.`member`, MATCH(companies_4.name) AGAINST ('bygg') AS `score`, `childCategories_4`.`name` AS `childCategory`, `companyAddresses_4`.`address`, `cities_4`.`name` AS `city`
FROM `nr_companies` AS `companies_4`
 INNER JOIN `nr_child_categories` AS `childCategories_4` ON childCategories_4.childCategoryid = companies_4.childCategoryid
 INNER JOIN `nr_company_addresses` AS `companyAddresses_4` ON companyAddresses_4.companyid = companies_4.companyid AND companyAddresses_4.primary = "1"
 LEFT JOIN `nr_cities` AS `cities_4` ON companyAddresses_4.cityid = cities_4.cityid WHERE (companies_4.phoneNumber = 'bygg') AND (companies_4.active = "1") 
)

UNION

(
 SELECT `companies_5`.`companyid`, `companies_5`.`childCategoryid`, `companies_5`.`registrationNumber`, `companies_5`.`name`, `companies_5`.`phoneNumber`, `companies_5`.`member`, MATCH(companies_5.name) AGAINST ('bygg') AS `score`, `childCategories_5`.`name` AS `childCategory`, `companyAddresses_5`.`address`, `cities_5`.`name` AS `city`
FROM `nr_companies` AS `companies_5`
 INNER JOIN `nr_child_categories` AS `childCategories_5` ON childCategories_5.childCategoryid = companies_5.childCategoryid
 INNER JOIN `nr_child_category_keywords` AS `childCategoryKeywords_5` ON childCategories_5.childCategoryid = childCategoryKeywords_5.childCategoryid
 INNER JOIN `nr_keywords` AS `keywords_5` ON keywords_5.keywordid = childCategoryKeywords_5.keywordid
 LEFT JOIN `nr_company_addresses` AS `companyAddresses_5` ON companyAddresses_5.companyid = companies_5.companyid AND companyAddresses_5.primary = "1"
 LEFT JOIN `nr_cities` AS `cities_5` ON companyAddresses_5.cityid = cities_5.cityid WHERE (MATCH(keywords_5.word) AGAINST ('bygg')) AND (companies_5.active = "1")
)

 ORDER BY `member` DESC, `score` DESC, `name` ASC LIMIT 20
and the EXPLAIN output is in the new attachment to this post

Re: UNION query going slow

Posted: Tue May 29, 2012 5:21 am
by Benjamin
Ok great. Now one thing most people don't know is that mysql will only use 1 index per query.

The explain results are saying that there are big problems in companies_3 and companies_5.

It's also saying there are no possible indexes, which likely means the database engine is doing a full table scan (many times).

Still haven't pinpointed the problem. Which table has 809,340 records? That is the table being scanned.

Re: UNION query going slow

Posted: Tue May 29, 2012 5:25 am
by Benjamin
Ah, it's childCategories_3.name and keywords_5.word I believe. Could be wrong. Try indexing those and see what happens.

Re: UNION query going slow

Posted: Tue May 29, 2012 5:34 am
by shiznatix
nr_child_categories.name and nr_keywords.word both have fulltext indexes and regular BTREE indexes on them (just added nr_child_categories.name BTREE index but keywords.word already had both). No increase in performance.

Table nr_companies has 809,340 records in it. Maybe if I did some of these queries in reverse (select from nr_keywords then join in companies through the join-tree for example) that could help or no?

Re: UNION query going slow

Posted: Tue May 29, 2012 5:36 am
by Benjamin
Still looking, but my first thought is to whether or not you can add a WHERE clause to 3 and 5?

Re: UNION query going slow

Posted: Tue May 29, 2012 5:54 am
by shiznatix
Benjamin wrote:Still looking, but my first thought is to whether or not you can add a WHERE clause to 3 and 5?
No super sure what you mean. I can add extra where clauses, no problem. Right now I have the following WHERE clauses

Code: Select all

//query 3
WHERE (MATCH(childCategories_3.name) AGAINST ('bygg')) AND (companies_3.active = "1")

//query 5
WHERE (MATCH(keywords_5.word) AGAINST ('bygg')) AND (companies_5.active = "1")

Re: UNION query going slow

Posted: Tue May 29, 2012 6:07 am
by Benjamin
Have you indexed the active field?

I don't think I am able to help without being able to play with the data and run various explains on queries. I would recommend that you run explain tests on 3 and 5, modifying both them and the indexes until you figure out what exactly the issue is.

Re: UNION query going slow

Posted: Tue May 29, 2012 8:06 am
by shiznatix
"active" was not indexed but now with an index, it does nothing. It is ENUM('0', '1') so I can't see an index being very helpful there.

I will play around with it but it just seams very strange that it is the child_categories table that is causing such issues. This table has only 563 rows in it while every other table that I am selecting off of has tons more. Sad but ok, I will post back if I can get more info.

Re: UNION query going slow

Posted: Wed May 30, 2012 5:06 am
by shiznatix
It is very strange, it seams that the query executes quickly (less than a second) but it takes a good while for the results to be returned and I can't quite figure out why. The query is query 3 and the explain looks like the attached screenshot.

Re: UNION query going slow

Posted: Wed May 30, 2012 9:44 am
by Benjamin
The name field needs to have a fulltext index or you should use LIKE instead of MATCH AGAINST

Re: UNION query going slow

Posted: Thu May 31, 2012 5:02 am
by shiznatix
Benjamin wrote:The name field needs to have a fulltext index or you should use LIKE instead of MATCH AGAINST
It does have fulltext. Check the attached screenshot