Slow Mysql Query Help

Hi guys
I have this mysql query.
Its running super slow.
Could someone have a look at it and suggest a better way it should be written.

Who is Participating?
ArgentiConnect With a Mentor Commented:
Here is a version you can test.
Of course it's super slow: you are running 3 (three!) subselects for each row in your main select.
Consider rewriting it by using JOIN on your tables (those situated in the sub-select sections)
Tomas Helgi JohannssonCommented:

Go through your query and make sure that you have indexes that match the columns in the where clauses of your query.
Indexes like
   on table theme_keywords_pyramidpromo_assigned ( userid, completed, dateDue,TaskTimeSingle)
   on table userprofiles (userid, companyID)

    Tomas Helgi
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Ray PaseurCommented:
Please read this article from one of our colleagues here at EE.

You might also want to learn about EXPLAIN SELECT, it can be very helpful!
Mark BradyPrincipal Data EngineerCommented:
Another great resource for making large query results come back lightning fast is to utilize memcache. Memcache stores the results of a large query and you save it using a unique key which is just a string of some sort. So you can make up a unique key using the fields in the query and store it in memcache then when you get a request for the same query you see if it is in memcache first. If so it returns really fast. If it is not then you go ahead and do your query then store it in memcache for next time.

Read up on the memcache class.
Ray PaseurCommented:
+1 for memcache.  At scale, this may be better than optimizing the queries.  But I would also be sure about the indexes, the normalization, etc.
matthewdacruzAuthor Commented:
Thank you very much Argeni.
Sorry for the delay, I was pulled onto another project.

That made a huge difference.
With a few additional tweaks I got the query time down to 0.688 s instead of 30.430 s

Thanks everyone else for the feedback

You're welcome, Matt. I'm glad it helped.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.