MySQL FULLTEXT Search Running Extremely Slowly

Question

Working on a small data set of less than 50 rows, the following query below works absolutely fine. When scaling this up and running the same query on a data set of around 5000 rows, this query takes around 3-6 seconds to run which is way too slow on a live environment.

What can be done to improve the performance of this query?

SELECT table1.ID, table1.CompanyName, 
(SELECT CompanyIDBeingFollowed FROM table2 WHERE PersonID = ? ) 
FROM table1 
JOIN table3 ON table1.ID = table3.ID  
WHERE table1.Status = 'Live'  AND  ( MATCH(table3.Content) AGAINST( '+search +term' IN BOOLEAN MODE ) )  
GROUP BY table1.CompanyID 
LIMIT 10;

In essence, the query above is searching through a large data set to rank the results using MySQL FullText so that the most relevant results are returned, i.e. the ones with the highest MySQL FullText Score. Then getting a few bits of information from two other tables based on what has matched, which is used to populate the useful content for the user on the page.

Thoughts?

I'm conscious that the current data set of around 5000 rows is going to grow into the hundreds of thousands in a short period of time, so I'd rather look at how to best optimise this query now rather than later.

On the development environment, the query runs instantly as this is on a data set of around 50 rows.

Update

I've just updated the SQL above to make it a bit easier to read and understand. Table summary below for reference.

Table 1 - Companies
 - ID
 - CompanyName

Table 2 - People Following Companies
 - CompanyIDBeingFollowed
 - PersonID

Table 3 - Pages On Company Website
 - ID (Unique)
 - CompanyID (Non-Unique)
 - Content

Answer 1

Difficult to answer your question, but if I where on your place I will try to do this:

1. Use explain to check what's going on there
2. Ensure that I realy need group by
3. Ensure that I realy need `(SELECT ID FROM table2 WHERE ID = ? ) as subquery
4. Ensure that I realy need order by

And also, may be, it will be acceptable to use 'like' search instead of full text search in case, when user filter data by one word.

Answer 2

This was taking around 3-6 seconds to run (code from original question);

SELECT table1.ID, table1.CompanyName, 
(SELECT CompanyIDBeingFollowed FROM table2 WHERE PersonID = ? ) 
FROM table1 
JOIN table3 ON table1.ID = table3.ID  
WHERE table1.Status = 'Live'  AND  ( MATCH(table3.Content) AGAINST( '+search +term' IN BOOLEAN MODE ) )  
GROUP BY table1.CompanyID 
LIMIT 10;

Whereas this code takes less than 1 second to run (as would be expected) - Removed the GROUP BY and used DISTINCT instead;

SELECT DISTINCT(table1.ID), table1.CompanyName, 
(SELECT CompanyIDBeingFollowed FROM table2 WHERE PersonID = ? ) 
FROM table1 
JOIN table3 ON table1.ID = table3.ID  
WHERE table1.Status = 'Live'  AND  ( MATCH(table3.Content) AGAINST( '+search +term' IN BOOLEAN MODE ) )  
LIMIT 10;

I've no idea why this is the case in this scenario, but it works. Would be great if anyone knows the more technical aspects of WHY this is the case and I'll update the answer.

Thanks for pointers all.