title: MySQL table with 10+ million rows - how to speed up searching using indexes?
body: <p>My table structure is as follows:</p>
  
  <blockquote>
    <blockquote>
      <p>CREATE TABLE IF NOT EXISTS <code>commodity_data</code> (<br>
        <code>dataid</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,<br>
        <code>commodity</code> smallint(6) NOT NULL,<br>
        <code>market</code> smallint(6) NOT NULL,<br>
        <code>quantity</code> float NOT NULL,<br>
        <code>price_min</code> mediumint(9) NOT NULL,<br>
        <code>price_max</code> mediumint(9) NOT NULL,<br>
        <code>price_modal</code> mediumint(9) NOT NULL,<br>
        <code>date</code> date NOT NULL,<br>
        <code>modified</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,<br>
        PRIMARY KEY (<code>dataid</code>)<br>
      ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7059415 ;  </p>
    </blockquote>
  </blockquote>
  
  <p>My SELECTs on this table will have WHERE clauses with one or more of 'commodity', 'market' and 'date' searched on.</p>
  
  <p>My ORDER BYs will be by price_min, price_max or price_modal and sometimes most of the other fields.</p>
  
  <p>The table will end up being over 10 million rows and will keep expanding by about 5 to 10 thousand a day.</p>
  
  <p>My server is currently a VPS dual 2.4Ghz xeon, 4GB RAM.</p>
  
  <p>The only index is currently on the 'dataid' field.</p>
  
  <p>I have read that setting up indexes can help and I think these should be on commodity, market and date, but I wanted to check if this is right before going ahead unless there's a better way of doing this. The table size will be around 600MB and growing.</p>
  
  <p>The 'commodity' and 'market' fields refer to the ID of commodities and markets in other tables. I will either LEFT JOIN or if it's faster, I will read those tables into arrays in PHP (simple one-level associative arrays id => name). There are around 300 commodities and 2,000 markets.</p>
  
  <p>Currently SELECTs are taking too long, and for example COUNT queries with a WHERE clause will take a minute or more.</p>