MySQL Forums
Forum List  »  Performance

Extranous methods in SQL query slowing things down?
Posted by: Dan Brown
Date: May 01, 2009 12:18PM

I'm the IT admin of a company and have been monitoring the slow query log for the investor section of a paticular site helping the programmers optimize the site so there isn't such a huge lag between the times that the request is sent to the server and the server starts sending data back. This is an example of a query which is done using the tsx, nyse (below), sp500, nasdaq, djia, gsptse, dowjones, and daxglobal exchanges. I'm not questioning their use of the exchanges, but each time whatever page load causes this, each of these queries are anywhere from 0.82s to 1.13s each depending on current server load on an average day. This is ridiculous.

SELECT sd.* FROM stock_data sd WHERE 1 AND sd.exchange = 'nyse' GROUP BY sd.id ORDER BY sd.pull_datetime DESC, sd.id DESC LIMIT 0,1;

id is an index, and also unique obviously.
pull_datetime is also nearly unique, although definitely unique when combined with sd.exchange.

Will MySQL recognize the stupidity of the statement and ignore the GROUP BY on the index? When I switch the statement to just

SELECT sd.* FROM stock_data sd WHERE 1 AND sd.exchange = 'nyse' ORDER BY sd.pull_datetime DESC, sd.id DESC LIMIT 0,1;

I get a query time from 0.92s to 0.61s. I don't know if this is really an improvement and MySQL is no longer doing a GROUP BY on the index so after analyzing this further I modified it to be just:

SELECT sd.* FROM stock_data sd WHERE 1 AND sd.exchange = 'nyse' ORDER BY sd.id DESC LIMIT 0,1;

And now the query takes only 0.0003s on average (cached queries for all of these take 0.0002s to 0.0001s).
So apart from the obvious amount of time which MySQL needs to order on a date string, will MySQL actually do a GROUP BY on a unique index?

Options: ReplyQuote


Subject
Views
Written By
Posted
Extranous methods in SQL query slowing things down?
3579
May 01, 2009 12:18PM


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.