troubleshooting Question

sub select in 'in clause'

Avatar of Turkish27
Turkish27 asked on
MySQL ServerSQL
5 Comments2 Solutions330 ViewsLast Modified:
Hi All,

I have just started using mySQL after a few years using MSSQL and I have hit a road block. For some reason the performance on a subselect within an 'in' clause in mySQL is abysmal.
I have a fairly simple query:

SELECT      SYMBOL, PRICE, SELLBROKER, TRADETIMESTAMP
FROM        dbname.TRADES
WHERE       SYMBOL IN ( SELECT      DISTINCT SYMBOL
                        FROM        dbname.TRADES
                        WHERE       PREVIOUSTICKDIRECTION < 1
                        GROUP BY    SYMBOL
                        HAVING      COUNT(SELLBROKER) > 3)
ORDER BY    SYMBOL, timestamp(TRADETIMESTAMP) ASC;

Open in new window


As it stands this takes 35 SEC (omg!) to run. If i run the subselect separately  it only takes .03s to run! If I grab the 200 records from it in a comma delimited list (i.e. 'AAA', 'BBB', 'CCC') and throw it in the IN clause manually the query runs super fast. So it is the subquery within the in clause itself that it does not like. MS SQL would handle this no problem. The only article on the net that I could actually find to explain this problem is:

http://spin.atomicobject.com/2011/03/25/mysql-in-query-performance/

I don't have the option (from what I know about the nature of this query) to do any type of join. I mean, it seems pretty straight forward, and I am surprised on the lack of documentation on this. Can anyone help please!!

The SYMBOL column is indexed as well. Originally I thought it was the problem but the fact that I can enter a comma delimited string explicitly and it runs fine shows that it isnt an index problem.

My table currently has 10k records in it but will have many more.

Thank you!
ASKER CERTIFIED SOLUTION
8080_Diver

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros