I'm having trouble figuring out which database design would be best (and less CPU heavy) for a project I'm working on.
Let's say I'm tracking stock prices each day for several thousand different stocks. I'm tracking the date, start price, end price, high price, and low price for each stock.
I'm eventually going to have a PHP page that let's me choose specific dates and displays the averages details for each stock for the dates selected.
So here's my issue. Would it be best for me to create a new table for each date, and on the PHP page go through all the tables to find the avearages for the list of stocks.
Would it be best for me to have one table with all the data on it?
I know initially it would be best for me to have one table with all the data on it, but if i had over a years worth of data on the table and wanted to search that table for a specific date range...wouldn't it be CPU heavy having to go through hundreds of thousands of records to find the specific data I queried?