Mysql – WHERE IN subquery too slow

existsMySQLoptimizationwhere

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:

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

Best Answer

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/

Related Solutions

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!

Mysql – Delete from table where multiple fields match select subquery from other table

Perhaps the DELETE JOIN would be preferable

DELETE A.* FROM table1 A INNER JOIN table2 b
ON  A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;

I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery

Even though there have been great strides in improving the Query Optimizer, evaluation of subqueries could make keys unavailable for key comparisons downstream.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
    SELECT A.id FROM table1 A INNER JOIN table2 b
    ON  A.id = B.id
    AND a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
    AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);