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.

Thanks
query.sql
matthewdacruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
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)
0
ArgentiCommented:
Here is a version you can test.
SlowQuery-Rewritten.sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
Hi!

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)

Regards,
    Tomas Helgi
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ray PaseurCommented:
Please read this article from one of our colleagues here at EE.
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

You might also want to learn about EXPLAIN SELECT, it can be very helpful!
0
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.
0
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.
0
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

Matt
0
ArgentiCommented:
You're welcome, Matt. I'm glad it helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.