Optimizing a query
Posted: Fri Apr 20, 2012 9:48 am
[EDIT] I just realized I posted this in the wrong form. Beautiful moderators, will you please move it for me?
Hi everyone,
So, I have this website that searches through a database of companies. The database structure is as follows:
[text]
companies
-companyid
-name
-member
-active
keywords
-keywordid
-word
company_keywords
-companyid
-keywordid
company_addresses
-companyAddressid
-companyid
-cityid
-address
cities
-cityid
-countyid
-name
counties
-countyid
-name
[/text]
Now, companies, company_addresses, keywords, and company_keywords have about 800,000 rows each. My search query uses 2 variables, $name and $location. The query looks like this:
now, while the query works just fine, it takes quite a long time. After various tests, it averages slightly over 5 seconds to run on localhost. This would be fine but this is a user facing form so it really has to be quicker than that. Plus, I first have to run a COUNT() query for pagination things so that puts even more time into the situation.
Is there a way to optimize this query? I have tried adding indexes everywhere I could but this only got it down to the current 5 seconds-per-query speed. Any advice is welcome and changes to the database structure are a-ok if it will help with preformance.
Hi everyone,
So, I have this website that searches through a database of companies. The database structure is as follows:
[text]
companies
-companyid
-name
-member
-active
keywords
-keywordid
-word
company_keywords
-companyid
-keywordid
company_addresses
-companyAddressid
-companyid
-cityid
-address
cities
-cityid
-countyid
-name
counties
-countyid
-name
[/text]
Now, companies, company_addresses, keywords, and company_keywords have about 800,000 rows each. My search query uses 2 variables, $name and $location. The query looks like this:
Code: Select all
SELECT
companies.companyid,
companies.name,
companies.phoneNumber
FROM
companies AS companies
LEFT JOIN
company_keywords AS companyKeywords
ON
companies.companyid = companyKeywords.companyid
LEFT JOIN
keywords AS keywords
ON
keywords.keywordid = companyKeywordsid.keywordid
LEFT JOIN
company_addresses AS companyAddresses
ON
companies.companyid = companyAddresses.companyid
LEFT JOIN
cities AS cities
ON
companyAddresses.cityid = cities.cityid
LEFT JOIN
counties AS counties
ON
cities.countyid = counties.countyid
WHERE
(
companies.name LIKE '%$name%'
OR
keywords.word LIKE '%$name%'
OR
keywords.word LIKE '%$location%'
OR
companyAddresses.address LIKE '%$location%'
OR
cities.name = '$location'
OR
counties.name = '$location'
)
AND
(
companies.active = '1'
)
ORDER BY
companies.member DESC, companies.name ASC
LIMIT 10
Is there a way to optimize this query? I have tried adding indexes everywhere I could but this only got it down to the current 5 seconds-per-query speed. Any advice is welcome and changes to the database structure are a-ok if it will help with preformance.