2

I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records.

A simple query like

SELECT t.price 
FROM commoddb.tbcommodprices t 
WHERE 
      t.ticker='LAV05 Comdty' 
   && t.whichprice='last' 
   && t.date = '2005-09-01'

takes over 1.6 seconds to respond to a local query, which seems very slow. I would appreciate any suggestions for ways to speed this up.

1
  • You should seriously not use DOUBLE for prices. Use DECIMAL with an appropriate precision instead. The same goes for the software using this data. stackoverflow.com/questions/1165761/…
    – Jannes
    Commented Jan 28, 2012 at 12:54

1 Answer 1

2

Here is your original query

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE t.ticker='LAV05 Comdty'
AND t.whichprice='last'
t.date = '2005-09-01';

Since there is but one table in this query, all you need is a good index.

ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date);

This index should now be a permanent part of the table until you decide to remove it, which I don't think you want to do. To see the table and the indexes, just do this:

SHOW CREATE TABLE commoddb.tbcommodprices\G
2
  • OK, that took 47 seconds and sped up my queries by a factor of 27x. Very impressive, thank you. One more question -- as I add additional data to the table, do I need to manually re-create the index, or will that be handled automatically by the server? Commented Jan 26, 2012 at 22:52
  • @MichaelStern - Once you add an index the database will always (unless otherwise intructed) keep it up to date with the data in the underlying table. (That's why, for example, adding too many indexes on a table will slow down your write activity. As you change the table, the database has to update all the relevant indexes on it.) Commented Jan 26, 2012 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.