I am working with a MySQL database. I am suppose to combine three select queries, to "improve performance". Each select below is dependent on the previous ID retrieved.
Here is the workflow to get data references between the tables.
There are three tables:
pUserMatch | pGroupMatch | ProfileData
----------- | ----------- | ----------
* GroupID .... GroupID
ProfileID .... ID
# get the group ID for the specific username
group_id = select GroupID from Profiles.pUserMatch where username = "<username>";
# now, get the group match ID for the group ID
profile_id = select ProfileID from Profiles.pGroupMatch where GroupID = "<group_id>"
# now, get the profile data for this ID
profile = select * from Profiles.ProfileData where ID = "<profile_id>"
# multiple select from tables
select usermatch.username, groupmatch.GroupID
from pUserMatch as usermatch, pGroupMatch as groupmatch
where usermatch.username = <"username">
# inner join...
select pUserMatch.GroupID, pGroupMatch.GroupID,
inner join pGroupMatch
on pUserMatch.GroupID = pGroupMatch.GroupID
I don't know if I understand your need, lets try:
Try to use this query:
select pGroupMatch.GroupID, ProfileData.ID from pUserMatch inner join pGroupMatch on pGroupMatch.GroupID = pUserMatch.GroupID inner join ProfileData on ProfileData.id = pGroupMatch.ProfileID where pUserMatch.username = "<username>";
Check if you can create indexes for improve your query, if you can try it:
CREATE INDEX idx_pUserMatch_01 ON pUserMatch (GroupID); CREATE INDEX idx_pGroupMatch_01 ON pGroupMatch (ProfileID);