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?