CODEWITHSUNDEEP
phpmysql
Randy
Randy

Reputation: 3

How to optimize a slow MySQL Query (via PHP)

I am attempting to run the following mySQL query via a piece of PHP code on the front end of a website. For some reason when I run this on my AWS instance the performance is abysmal. However, when I run it localhost (wamp) I have no problems at all. It will even return results for larger data sets. I am somewhat of a novice when it comes to mySQL. Thoughts?

SELECT *
FROM bigfoot
WHERE family_id IN
(
   SELECT family_id AS family_id 
   FROM bigfoot 
   WHERE user_email = '$email' 
   GROUP BY family_id
)

As you can see, the primary element in gathering specific row data for this query is family_id. However, only the first row of each family_id contains an email address of which in this case is used as the basis for the query. I have searched various posts in the form, but am still unsure of how to move forward.

Thanks in advance,

Randy

Upvotes: 0

Views: 38

Answers (1)

Drew
Drew

Reputation: 24970

I will go out on a limb, why not.

I think this would be blazing fast:

select b1.*
from bigfoot b1
join bigfoot b2
on b2.family_id=b1.family_id
and b2.user_email='$email'

assuming you had a composite index on bigfoot (family_id,email) ... a big assumption. plus an index on email

It does show however how to lose the correlated subquery.

b2 would be isolated to email, getting family. b1 will be all for that family, if I did it right

Upvotes: 1

Related Questions