mySQL fulltext searches as subqueries

Posted on 2009-02-10
Last Modified: 2012-08-14
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?
Question by:sjamal_hsj
    1 Comment
    LVL 17

    Accepted Solution

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

    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;

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now