Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Jan 28 '12 at 12:54

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
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? –  Michael Stern Jan 26 '12 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.) –  Nick Chammas Jan 26 '12 at 23:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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