mySQL fulltext searches as subqueries
Posted on 2009-02-10
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:
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
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
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?