UNION query going slow

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

UNION query going slow

Post 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.
Attachments
Screenshot.png
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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
Attachments
Screenshot-2.png
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post by Benjamin »

Ah, it's childCategories_3.name and keywords_5.word I believe. Could be wrong. Try indexing those and see what happens.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post by Benjamin »

Still looking, but my first thought is to whether or not you can add a WHERE clause to 3 and 5?
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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")
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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.
Attachments
Screenshot.png
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: UNION query going slow

Post by Benjamin »

The name field needs to have a fulltext index or you should use LIKE instead of MATCH AGAINST
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: UNION query going slow

Post 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
Attachments
Screenshot-1.png
Post Reply