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:
(If the image with the explain doesn't show, it's also here: https://oi41.tinypic.com/2iw4t8l.jpg)
