[Solved] Optimizing sub-queries, making two queries become one

EverSQL Database Performance Knowledge Base

Optimizing sub-queries, making two queries become one

Database type:

The following query is used to do a member search, in this example, only using the last name. The query returns in a few seconds if searching on a full matching name; but if :LastName = 'S', then the query takes upwards of 12 seconds to return.

How can I speed up this query? If I can do it in under a second with two queries, shouldn't I be able to make it just one query, just as fast? Because of plugins and other methods, it would be easiest for me to have this be one query, hence my question.

The Member table holds every member we've ever had. The table has some members who we don't have any registration for, so they only exist in this table, not in Registration or Registration_History. Registration_History has extra information on most members that I want to display. Registration has most of the same information as RH (RH has some fields that Reg doesnt), but sometimes it has members that RH does not have, which is why it is joined here. EDIT: Members can have multiple rows in Registration. I want to fill the columns from Registration_History, however, some legacy members ONLY exist in Registration. Unlike other members,these legacy members only ever have 1 row in Registration, so I don't need to worry about how Registration is sorted, just that it only grabs 1 row from there.

SQL Fiddle with sample database design

MemberID is indexed in all 3 tables. Before I put the SELECT RHSubSelect.rehiId subquery in, this query was taking almost a full minute to return.

If I split the query into 2 queries, doing this:

SELECT
    MemberID
FROM
    Member
WHERE 
    Member.LastName LIKE CONCAT('%', :LastName, '%')

And then putting those MemberIDs into an array and passing that array to RHSubSelect.MemberID IN ($theArray) (instead of the Member subquery), the results come back very quickly (about a second).

Full query: (Full SELECT statement is in the Fiddle, SELECT * for brevity)

SELECT
    *
FROM
 Member
    LEFT JOIN
        Registration_History FORCE INDEX (PRIMARY)
            ON
                Registration_History.rehiId = (
                                                SELECT
                                                    RHSubSelect.rehiId
                                                FROM
                                                    Registration_History AS RHSubSelect
                                                WHERE
                                                    RHSubSelect.MemberID IN (
                                                                                SELECT
                                                                                    Member.MemberID
                                                                                FROM
                                                                                    Member
                                                                                WHERE 
                                                                                    Member.LastName LIKE CONCAT('%', :LastName, '%')
                                                                            )                                                                   
                                                ORDER BY 
                                                    RHSubSelect.EffectiveDate DESC
                                                LIMIT 0, 1
                                            )                                   
    LEFT JOIN
        Registration FORCE INDEX(MemberID)
            ON
                Registration.MemberID = Member.MemberID
WHERE 
    Member.LastName LIKE CONCAT('%', :LastName, '%') 
GROUP BY
    Member.MemberID
ORDER BY 
    Relevance ASC,LastName ASC,FirstName asc 
LIMIT 0, 1000

MySQL Explain, with the FORCE INDEX() in the query: "Mysql Explain"

(If the image with the explain doesn't show, it's also here: https://oi41.tinypic.com/2iw4t8l.jpg)

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: 6): The database will not use an index when using like searches with a leading wildcard (e.g. '%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
The optimized query:
SELECT
        Member.MemberID 
    FROM
        Member 
    WHERE
        Member.LastName LIKE CONCAT('%', :LastName, '%')

Related Articles



* original question posted on StackOverflow here.