• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1381
  • Last Modified:

mySQL fulltext searches as subqueries

I have the following database:

table "companies": 100000 rows, 14 columns of info about each company
each company has "keywords" which is indexed (fulltext)
each company can have multiple "regions" which it serves, these are stored in
table "regions": 180000 rows

I have tried running the following two queries, but they seem to time out:

Query 1
SELECT t1.* ,MATCH (keywords) AGAINST ('auto') AS score FROM companies AS t1, regions AS t2 WHERE MATCH (keywords) AGAINST ('auto') AND (t2.region_id='91203' AND t1.company_id = t2.company_id) ORDER BY score

Query 2
SELECT *, MATCH (keywords) AGAINST ('auto') AS score FROM companies WHERE MATCH (keywords) AGAINST ('auto') AND company_id IN (SELECT company_id FROM regions WHERE region_id='91203') ORDER BY score

It occurred to me that the subquery of selecting from regions would create a much larger table (there are about 60000 rows with region_id='91203'), which is why it might be timing out.
So I reversed the order of the queries as below, but in that case I am unable to sort by relevance

Query 3
SELECT * FROM regions WHERE region_id='91203' AND company_id IN (SELECT company_id FROM companies WHERE MATCH (keywords) AGAINST ('auto'))
Return 659 Records in 0.044 sec

So, my questions are:

1. Is it possible to re-arrange query 3 to sort by relevance?

2. Part of the problem is that I don't know how many "regions" a company can service. If I set a maximum at 5 and create 5 new columns in companies called "region1", "region2" etc. and then added the clause:
WHERE (region1='91203' OR region2='91203' OR ...)
would this be faster? My problem with this is that it isn't very elegant, and restricts me to five "regions"

3. Why are these subqueries or joins taking so LOONG?
0
sjamal_hsj
Asked:
sjamal_hsj
1 Solution
 
akshah123Commented:
The first query or the same query represented with inner join as below:

SELECT t1.* ,MATCH (keywords) AGAINST ('auto') AS score
FROM companies AS t1
INNER JOIN regions AS t2 ON t1.company_id = t2.company_id
WHERE MATCH (keywords) AGAINST ('auto') AND t2.region_id='91203'  
ORDER BY score;

should produce the result the quickest in most scenarios.  If the above query is not returning data quickly, you most likely have one of the following problem:

* company_id is not the primary key of companies table.
* company_id is not setup as foreign key on regions table or there is an index missing on company_id column in regions table. (You either need it as a foreign key or an index)

If you find that above is the case and that solves the problem ... great.

Otherwise, please post result of EXPLAIN on the query that i provided. So run...

EXPLAIN
SELECT t1.* ,MATCH (keywords) AGAINST ('auto') AS score
FROM companies AS t1
INNER JOIN regions AS t2 ON t1.company_id = t2.company_id
WHERE MATCH (keywords) AGAINST ('auto') AND t2.region_id='91203'  
ORDER BY score;
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now