MySQL Forums
Forum List  »  Stored Procedures

Re: alternate of temporary table?
Posted by: Rick James
Date: March 30, 2014 10:45PM

Before tackling the temp table question, let's do some performance improvements...

> (e.description LIKE CONCAT('%', IFNULL(p_search_key,'%'), '%')

I suspect this is because of some optional arguement on a UI? If so, then construct the query without that clause. It is in an OR clause, so it is hard to optimize when it is there.

> IN ( SELECT ... )

optimizes poorly. Change it to a JOIN if possible.

> ( SELECT COUNT(*) ... ) + ( SELECT COUNT(*) ... )

Those are using the same table. Might it be counting the same row(s) twice? If not, then how about a single SELECT:
( SELECT SUM(approvalStatus='Approve') + SUM(acceptance='Accept') ... )
Note that booleans evaluate to 0 or 1, then SUM() effectively does a COUNT.

> OR (e.maximumAttendeesAllow = 0)

That is totally predictible outside the subquery; find a way to avoid doing the subquery in that case.

Check your INDEXes and EXPLAIN plans to see if it is using the indexes you expect.

Consider not computing the distance until the outer query.

A FETCH loop is slow; see if you can avoid it.

Your code seems to be looking for the "nearest" item(s); such inherently involves a table scan. The code here is significantly faster for large datasets:

Options: ReplyQuote

Written By
March 28, 2014 06:07AM
Re: alternate of temporary table?
March 30, 2014 10:45PM
March 31, 2014 02:18AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.