I am working with a mysql database of stock market transactions. I currently have a table for each day's ticks. There are approximately 14 million ticls per day, hence each table has about 14 million rows.
I have indexes on SerialNumber (bigint unique) and Symbol (varchar 20).
Executing a simple query like
Select datetime FROM ticks WHERE symbol='ibm';
returns about 6000 rows, but can take up to 20 seconds to execute. Running the same query again executes almost instantly, suggesting that the results are buffered.
What can I do to make this faster?
I have considered separating the more active symbols into a separate database, or even using a table per symbol. This would almost certainly make the queries faster, but at the expense of having a huge number of tables - there are, including futures and options, some 300,000 symbols. Most of them have little or no data, but all of them have to be accomodated.
I suppose I could have a table with pointers to which table contains the actual data, only having entries for the more active issues which would be stored in separate tables. If a symbol doesn't have an entry in this index, then its data is stored in the main table.
I guess a question that needs to be answered is how many tables is too many tables?
This, by the way is running on Windows XP with a Raid 0 array. Core 2 Duo Processor, 2G ram, and the computer is not doing much other than running this database and collecting data from our service.
These are, by the way, MyISAM tables.
It just seems to me that this should run faster.
Any suggestions? And, while we're at it, would this work better in MS SQL server?
Start Free Trial
