[Solved] How to optimize a wildcard search with an OR in mysql

EverSQL Database Performance Knowledge Base

How to optimize a wildcard search with an OR in mysql

Database type:

I am in a situation where I need to do a wildcard search. Full Text Index is no help as people are searching for part of a word and I can't strip the % from the start of the search to speed it up because they may be searching for the middle part.

There are two fields that need to be searched and a match in either should be returned. If I search for just one field it is very quick (there aren't many rows) but as soon as I search in both it's really slow.

I won't go into details, but to give an idea, when I have

... AND (T1.Field1 LIKE '%search%')

or 

... AND (T2.Field1 LIKE '%search%')

It takes 0.0695 seconds

but when I do

... AND (T1.Field1 LIKE '%search%' OR T2.Field1 LIKE '%search%')

it takes 35 seconds

How can I optimize this and why does the OR increase the search time by so much?

The whole, unedited query as requested:

SELECT CDR.Status AS CDRStatus, D.VendorID, D.RevisionOfID, D.Revision, D.DocumentID, D.Title, D.OriginalFilename 
FROM Documents AS D
LEFT JOIN CompanyDocumentReviews AS CDR ON CDR.DocumentID = D.DocumentID
LEFT JOIN Vendors AS V ON V.VendorID = D.VendorID
LEFT JOIN VendorAliases AS VA ON VA.Vendor1ID = V.VendorID AND VA.Vendor2ID != V.VendorID
LEFT JOIN Vendors AS V2 ON V2.VendorID = VA.Vendor2ID
WHERE D.Status != 'Deleted' AND (V1.VendorName LIKE '%search%' OR V2.VendorName LIKE '%search%')

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 27): The database will not use an index when using like searches with a leading wildcard (e.g. '%search%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Avoid LIKE Searches With Leading Wildcard (query line: 28): The database will not use an index when using like searches with a leading wildcard (e.g. '%search%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `CompanyDocumentReviews` ADD INDEX `companydocumentrev_idx_documentid` (`DocumentID`);
ALTER TABLE `Documents` ADD INDEX `documents_idx_status` (`Status`);
ALTER TABLE `VendorAliases` ADD INDEX `vendoraliases_idx_vendor1id` (`Vendor1ID`);
ALTER TABLE `Vendors` ADD INDEX `vendors_idx_vendorid` (`VendorID`);
The optimized query:
SELECT
        CDR.Status AS CDRStatus,
        D.VendorID,
        D.RevisionOfID,
        D.Revision,
        D.DocumentID,
        D.Title,
        D.OriginalFilename 
    FROM
        Documents AS D 
    LEFT JOIN
        CompanyDocumentReviews AS CDR 
            ON CDR.DocumentID = D.DocumentID 
    LEFT JOIN
        Vendors AS V 
            ON V.VendorID = D.VendorID 
    LEFT JOIN
        VendorAliases AS VA 
            ON VA.Vendor1ID = V.VendorID 
            AND VA.Vendor2ID != V.VendorID 
    LEFT JOIN
        Vendors AS V2 
            ON V2.VendorID = VA.Vendor2ID 
    WHERE
        D.Status != 'Deleted' 
        AND (
            V1.VendorName LIKE '%search%' 
            OR V2.VendorName LIKE '%search%'
        )

Related Articles



* original question posted on StackOverflow here.