title: How to optimize a large mysql table (stock data)
body: <p>I have a table with following structure,</p>
  
  <pre><code>    `trading_daily_price` (
      `id` int(11) NOT NULL PRAMARY AUTO_INCREMENT,
      `date` date DEFAULT NULL,
      `Symbol` varchar(20) DEFAULT NULL,
      `Market` varchar(12) DEFAULT NULL,
      `QuoteName` text,
      `Price` float DEFAULT NULL,
      `PriceChange` float DEFAULT NULL,
      `PriceChangePct` float DEFAULT NULL,
      `Volume` float DEFAULT NULL,
      `DayLow` float DEFAULT NULL,
      `DayHigh` float DEFAULT NULL,
      `Week52Low` float DEFAULT NULL,
      `Week52High` float DEFAULT NULL,
      `Open` float DEFAULT NULL,
      `High` float DEFAULT NULL,
      `Bid` float DEFAULT NULL,
      `BidSize` float DEFAULT NULL,
      `Beta` float DEFAULT NULL,
      `PrevClose` float DEFAULT NULL,
      `Low` float DEFAULT NULL,
      `Ask` float DEFAULT NULL,
      `AskSize` float DEFAULT NULL,
      `VWAP` float DEFAULT NULL,
      `Yield` float DEFAULT NULL,
      `Dividend` char(12) DEFAULT NULL,
      `DivFrequency` varchar(24) DEFAULT NULL,
      `SharesOut` float DEFAULT NULL,
      `PERatio` float DEFAULT NULL,
      `EPS` float DEFAULT NULL,
      `ExDivDate` date DEFAULT NULL,
      `MarketCap` float DEFAULT NULL,
      `PBRatio` float DEFAULT NULL,
      `Exchange` varchar(32) DEFAULT NULL,
      `NewsTitle` varchar(1024) DEFAULT NULL,
      `NewsSource` varchar(32) DEFAULT NULL,
      `NewsPublicationDate` date DEFAULT NULL,
      `NewsURL` varchar(256) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>I didn't find an idea to break down it, in frontend presentation, I need all these columns to display. I am writing a query like,</p>
  
  <pre><code>    SELECT * FROM trading_daily_price WHERE date='SOME_DATE' AND Symbol='%search_key%' ORDER BY 'column' LIMIT 10
  </code></pre>
  
  <p>The table has millions of records, and every day new records are added. Now the problem is every query takin so much time to generate the output. In a 4GB VPS with DigitalOcean with some configuration, it's running nicely. But, in Godaddy business hosting it's running very slowly.</p>
  
  <p>I want to know is it a better idea to break the columns into multiple tables, and using JOIN statements. Will it increase performance? or I need to follow other optimization logic.</p>
  
  <p>As suggested by Madhur, I have added INDEX to date, symbol, and Market. It improves the above query speed, but the following query still taking much time.</p>
  
  <pre><code>    SELECT `date`,`Price` FROM trading_daily_price WHERE `Symbol` = 'GNCP:US' ORDER BY date ASC
  </code></pre>
  
  <p>Thanks in advance,
  Rajib</p>