CODEWITHSUNDEEP
mysqlsql
Greg
Greg

Reputation: 87

mysql full text search with union performing slowly

I have a database that I had to move from MS SQL over to MySQL. I was able to migrate it over and get things (mostly) up and running, but the performance has taken a big hit on the following query.

In MS SQL, the query would run in less than 2 seconds, but it's now taking 1-2 minutes in MySQL. In MS SQL it was using the CONTAINS operator rather than MATCH, but when I moved to MySQL I made the subject and note columns full text indexes.

There are two separate tables - both containing a "subject" and "full note" field. The database is quite large and I'm trying to do a free text search, looking for a match in either field in either table.

If someone can help me optimize the query, I'd really appreciate it. 

SELECT cs.SWCaseID, cs.SWSubject, cs.SWNote
FROM tblSCasesSearch cs
WHERE cs.SWCaseID in
    (SELECT cs.SWCaseID 
    FROM tblSCasesSearch cs 
    WHERE MATCH (cs.SWSubject, cs.SWNote) AGAINST ('SEARCH VALUE' IN BOOLEAN MODE)
union
    SELECT csn.SWCaseID
    FROM tblSCaseNotesSearch csn
    WHERE MATCH (csn.SWSubject, csn.SWNote) AGAINST ('SEARCH VALUE' IN BOOLEAN MODE))
LIMIT 50

Upvotes: 1

Views: 131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

Try rewriting the query as explicit joins:

SELECT cs.SWCaseID, cs.SWSubject, cs.SWNote
FROM tblSCasesSearch cs left outer join
     (SELECT distinct cs.SWCaseID 
      FROM tblSCasesSearch cs 
      WHERE MATCH (cs.SWSubject, cs.SWNote) AGAINST ('SEARCH VALUE' IN BOOLEAN MODE)
     ) scs
     on cs.SWCaseId = scs.SWCaseId left outer join
     (SELECT distinct cs.SWCaseID 
      FROM tblSCaseNotesSearch cs 
      WHERE MATCH (cs.SWSubject, cs.SWNote) AGAINST ('SEARCH VALUE' IN BOOLEAN MODE)
     ) scns
     on cs.SWCaseId = scns.SWCaseId
WHERE scs.SWCaseId is not null or scns.SWCaseId is not null
limit 50;

Upvotes: 1

Related Questions