1

I have an MEMORY MYSQL database that contains a table with 200k+ rows. I use this database to test trading strategies, so it is queried repeatedly ad nauseum. No new data is added to the database.

One column in this database's primary table is "Time of day". At each query my stored procedure selects those rows where the "Time of day" falls a maximum of 2 hours before or 2 hours after the query input "Time of day".

Since the data in my database has a 5-minute resolution, there are total of 288 possible "Times of day" (24 * 60/5 = 288).

This means that each query selects roughly 1/288 of the table. Furthermore, that means that there is a huge overlap between query A and query B (which immediately follows).

At this moment, there is simply 1 huge table which gets filtered at each query. This is (way too) slow.

I have been trying to figure out a more optimal setup. I have considered creating 288 pre-filtered tables and simply access them at query time. That, however, takes too much RAM and ends up slowing the query instead of speeding it up.

Are there any more optimal solutions?

Mike Furlender
  • 3,869
  • 5
  • 47
  • 75
  • 4
    assuming here that you have basic things like index's etc on the right columns. have you checked the query plans how to see if it is using the indexes as you expect it to. do you have enough memory to hold the entire database, if it is going to swap disk then it will be no faster then a disk based table – bumperbox Oct 18 '11 at 02:32

1 Answers1

1

You could simply add an integer column to the table to indicate which "window" the transaction is in. So the field would hold a value between 1 and 288 for each record which would need to be updated in a one-time update.

After that querying the table would simply be:

select * from mytable where window = 123

for example. It is essential to add an index to this column.

Having said this, I'd be surprised if this performs much better than just querying against the date column (assuming that is already indexed of course) but might be worth a try.

Filtering a 200k row table with an index on a single field really should be very quick. Have you verified that the delay is caused by the database engine and not the retrieval of the results?

njr101
  • 9,499
  • 7
  • 39
  • 56
  • Thats a great idea, I will give it a try. By slow I mean like 0.1 seconds. For my purposes this is very slow though. – Mike Furlender Oct 18 '11 at 15:37
  • @Mike it may have been useful to mention your definition of slow in the question. A lot of questions on stackoverflow are from newbies, so when you say slow, usually people are thinking in terms of 10's of seconds to minutes – bumperbox Oct 18 '11 at 19:29