Click here to Skip to main content
15,662,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 3 tables that I've joined and for some reason my query is VERY slow, takes 30secs - 1min to execute.

I have no experience at writing SQL other than writing simple SELECT statements or calling a storedProc that a DB developer wrote.

//trdopt rows > 100,000
//instropt rows > 100,000
//exchopt rows = 10;

SELECT, instr.sym, instr.under, instr.exp,, trddata.prc
FROM   trdopt trddata
JOIN   instropt instr
ON     trddata.optid =
JOIN   exchopt exch
ON     trddata.exchcode =
WHERE DATE_FORMAT(, '%Y-%m-%d %T') = '2013-04-02';

Can someone please help me out and show/explain what the problem is and the correct way to implement this query.

Thanks in advance,
Updated 4-Apr-13 14:39pm

1 solution

It is likely to be a lack of indexes, this is always the first place to check.

Turn on the Actual Execution plan (icon in SSMS toolbar) and run the query. When it is completed you will have a tab with the execution plan used. You MAY have a nice little green section recommending additional indexing, copy/paste this to a new query window and create the indexes. Rerun your query.

Query optimisation is an ART not a science, it takes experience, your best bet would be to start reading up on how to use the Execution plan.
Share this answer
d.allen101 4-Apr-13 22:19pm    
Thanks for the advice but I don't know what "Acutal Execution" is and also the DB was set up by my manager so I can't ALTER any tables. By the way it's an MySql DB if that makes a difference. Basically what's going on is I'm a front-end dev and I'm querying the DB to render historial stock data to be analyzed
Mycroft Holmes 4-Apr-13 22:31pm    
Sorry I assume SQL Server. If you have no access to the database other than query then you are screwed, go back to your manager and make him optimise the query.

I have never used MySQL but after the indexing I would look into the where clause which seems to be converting a date to a string and then applying the filter I think it should be convert the string to a date and filter on the date data type.
d.allen101 4-Apr-13 22:35pm    
He told me that MySQL indexes foreign keys by default. So you don't see anything wrong with my query? It can't be...Even if I put a LIMIT on the number of rows to be returned e.g. (LIMIT 1000) it still takes at least 30secs. Something is not right here.
Mycroft Holmes 4-Apr-13 22:46pm    
I don't have any MySQL experience so can't refute the indexing issue, 30sec is not unreasonable on a large database. Try changing the where clause.
d.allen101 5-Apr-13 7:35am    
Hey Mycroft Holmes, THANK YOU! I removed that DATE_FORMAT() and it helped sooooo much!!!! it didn't resolve my issue but it DEFINITELY helped, so at least I'm now making progress! Thanks again. If you have any other suggestions plz let me know!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900