1

I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k rows, and employees table has 82k rows).

Desired result: get search results for "company ABC" in table A only if we have employees data for that company in table B.

The query I have is:

SELECT *,
        MATCH (name, keywords, description) AGAINST ('+searchKeyword*') as relevance,
        (SELECT COUNT(customerID) FROM customers WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE) AND customerID IN(SELECT customerID FROM customersPersonelData)) as totalResults
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY relevance DESC, name ASC
LIMIT 0,10

For testing I removed "relevance" and "totalResults" subqueries, and this query then takes 72 seconds:

SELECT *
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY name ASC
LIMIT 0,10

Query explanation: enter image description here

I tried using EXIST and JOIN, the result is the same. How can I speed this query up?

  • From EXPLAIN plan, it seems that you dont have full text indexes defined on the columns mentioned in your query. Can you run SHOW CREATE TABLE customers and give out the results of the same. You should have a composite Full Text Index on (name, keywords, description) columns in the customers table – Madhur Bhaiya Oct 6 at 12:35
  • There are composite full text indexes. Name, keywords and description is just for an example.. – Tom Oct 6 at 12:44
  • Your title says it all. Avoid that construct. – Rick James Oct 14 at 18:35
  • What version of MySQL? Older versions would need an explicit temp table; 5.6 and 5.7 probably best work with "autokey"; 8.0 may have better tricks. – Rick James Oct 14 at 18:39
1

If I understand correctly, you are getting count to satisfy your Pagination requirements. Generally, two separate queries are executed; one to get the 10 records, and another to get the total count.

The problem in your approach is that you are calculating the Count in a subquery inside the SELECT clause. For every row returned by the SELECT clause, is going to be executing the same count again and again. You can move the count calculation part to a Derived Table instead (Subquery inside the FROM clause), and CROSS JOIN it.

Another noteworthy thing is that MySQL is (unfortunately) generally inefficient when optimizing subqueries inside the IN(...) condition. You can convert this into EXISTS based Correlated Subquery instead (JOIN may result in Duplicates).

SELECT c.*,
       MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*') as relevance,
       dt.totalResults         
FROM customers c 
JOIN customersPersonelData cpd ON cpd.customerID = c.customerID 
CROSS JOIN 
( 
  SELECT COUNT(c1.customerID) AS totalResults
  FROM customers c1
  WHERE MATCH (c1.name, c1.keywords, c1.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE) 
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd1 
                WHERE cpd1.customerID = c1.customerID)
) dt
WHERE 
  MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE) 
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd 
                WHERE cpd.customerID = c.customerID)
ORDER BY relevance DESC, c.name ASC
LIMIT 0,10

For good performance, you would need an index on customerID in customersPersonelData table. Also, a composite Full Text Index should exists on (name, keywords, description) columns in the customers table.

Now, if I were you, and this was a Pagination problem, I would look at this article to rethink how pagination should be done: https://mariadb.com/kb/en/library/pagination-optimization/

  • You are right about the total count. I already tried without it and the result is much better (70-130 seconds) depending on the search keyword / number of results.. But that is still way to slow. Also, I tried EXISTS, actually it performs worse in this case. I stopped the query after 5 minutes. Any other ideas? :) – Tom Oct 6 at 12:56
  • @Tom from your EXPLAIN plan, it does not seem to be using the Full Text Index. Can you please provide the table structure ? Run SHOW CREATE TABLE... and please report its result. – Madhur Bhaiya Oct 6 at 12:58
  • What type of index for customerID in cpd? I already have composite FullText indexes in customers table. – Tom Oct 6 at 12:59
  • @Tom you need index in customerPersonelDetails table also, otherwise subquery will be slow. Please define a normal btree index on customerID in that table also – Madhur Bhaiya Oct 6 at 13:00
  • Here is the SHOW result: ibb.co/WB2D482 – Tom Oct 6 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.