MySQL - 15 million rows or 300,000 tables : Which is a better choice?

I think your problem is lack of normalization. The query should be like the following:'
Select datetime FROM ticks WHERE symbol=999;

You need to create a symbol table like the following:
create table symbol (id int not null auto_increment, symbol varchar(100), primary key(id));
insert into symbol values (null, 'ibm');

This should make your select statement run dramatically faster, and it should also reduce the space of your database because your database would not have to save the string 'ibm' for each row.

Here is the structure of a typical day's table:

CREATE TABLE `ticks_20070911` (
  `SerialNumber` bigint(20) NOT NULL auto_increment,
  `Symbol` varchar(20) NOT NULL default '',
  `DateTime` datetime default NULL,
  `Price` int(11) default NULL,
  `PriceType` int(11) default NULL,
  `Size` int(11) default NULL,
  PRIMARY KEY  (`SerialNumber`,`Symbol`),
  UNIQUE KEY `Serial Number` (`SerialNumber`),
  KEY `Symbol` (`Symbol`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

There is a table for each day, and all the ticks for the day are put in sequentially. I receive the data over the internet and (through the data vendor's API) extract the ticks and insert them into the table. I NEVER go back and edit a tick.

So, once a day is passed, there is no data added to that day's table. Are you syuggesting that I should reindex the table after the day is complete?

I do not currently do date queries, as each date is in a separate table. I also do not do time queries, as the application program only requests whole days. I do serialnumber queries on the real-time data, to only get the newest ticks. This doesn't appear to be a problem.

If I'm understanding you correctly, the lookup table would have pointers into the data table that contain the serial number of each tick for a given symbol. Would that really be any better than having a separate table for each symbol?

There's never too many tables must have some practcal limit, no? Each table has three files, there are over 300,000 symbols, so we're talking a million files or so. Somehow that seems excessive from the disk management standpoint. Making a backup could take forever, I think.

Thanks much.

hulagutten -

Do I understand this correctly that I would then be referencing the ticks by a newly created symbol number, and that the requisite two queries (one to obtain the symbol number from the symbol name table and the other to obtain the actual data) would be faster than the original query?

That's certainly an interesting approach, and probably not too hard to implement.

In writing the data, however, there would be at least two accesses per tick. One to get the symbol number, and one to write the tick data. There would have to be a third access to write to the symbol name table if the symbol didn't previously exist (there are some symbols that trade very infrequently).

I wonder how nasty this might be in processing incoming data.

Yes you understand it correctly. I do not know if it helps, but the following is a Java example (from our code) of the code needed to get the ID of a tick (if it does not already exist in the table it needs to be inserted). Notice that it uses something called prepared statements to speed up the process.


      public int registerIna_V1(String ina) throws Exception {
            if(ina.equals("")) return -1;
            try {
                  int inaId = 0;
                  PreparedStatement ps = db.prepStat()[registerIna_V1_SELECT];
                  ResultSet inaRS = ps.executeQuery();
                  if(inaRS.next())
                        inaId = inaRS.getInt("id");
                  else {
                        ps = db.prepStat()[registerIna_V1_INSERT];
                        ps.setString(1, ina);
                        ps.executeUpdate();
                        inaId = (int) db.lastInsertId(ps);
                  }
                  inaRS.close();
                  return inaId;
            } catch (SQLException e) {
                  e.printStackTrace(log.err);
                  throw new Exception("Ina|Could not register Ina! " + e.getMessage());
            }

      }

You would probably loose some performance on inserts, but the database would become a lot easier to manage.

A couple of good links when dealing with data warehouses in MySQL:
http://jpipes.com/presentations/perf_tuning_best_practices.pdf
http://jpipes.com/presentations/index_coding_optimization.pdf

hulagutten -

Thanks for all that, it makes a lot of sense. However, in your suggestion:

   create table symbol (id int not null auto_increment, symbol varchar(100), primary key(id));
   insert into symbol values (null, 'ibm');

Wouldn't I also want to have an index on the symbol field? That's where my queries will be aimed.

Yes you would need an index on the symbol column in the symbol table. The symbol table would however, "just" contain 300.000 rows, not millions like in the ticks_20070911 table. You also need to put a key on the symbol column in the ticks_20070911 table, of course...

Just out of curiosity, how much historical data do you have? I bought such data a while ago from all tickers on the nasdaq index, they had data back to 2003.

but that symbol column is now an integer, not a string. Correct?

I have tick data going back to 11/11/2005. This doesn't cover all of the exchanges. I have very full data going back about a year, and more full data going back just a couple of months. I don't have bid/ask data for most of it, as that would exceed the capacity of my T1.

I'm looking at purchasing some daily data going back about 10 years or so.

Please write me at daa.alps ( a t ) gmail ( d o t ) com

Yes the symbol column is now an integer, which is a lot faster to run queries against.

Putting your data in the new schema should reduce the size significantly. You could also use MySQL compression or maybe the Archive Storage Engine.

Hi,

What exactly are you using this data for?

I would suggest that if you are not using it for transaction based applications and are using it for trending and analysis then I would suggest that you do not use a traditional transaction based OLTP approach, I would suggest for reporting and analysis then you use an OLAP or Cube based databases instead.  This will dramatically increase the speed of your queries and make them easier to write once it is setup.

Raynard7- Can MySQL do OLAP? How is it done?

Mysql can not do olap however you need the correct tools for the job.  Whilst I think MySQL is excellent for transactional systems it, like most transactional databases  is not primarily designed for the sorts of data mining, trending or large scale analysis over huge datasets.  If however the aim for this system was to just store daily information for a long period of time and only a few days worth of data was required then it would be what I would recommend.

If large scale analysis is what is being done then I would recommend that OLAP be used, which is designed with this goal as its primary purpose.  Mondrian (now part of pentaho) is a good open source olap tool that can be used to pull its data from mysql http://mondrian.pentaho.org/ and there are other open source olap databases on the market.

OK, so I coded the whole thing up so that the symbols file looks like:

CREATE TABLE `symbols` (
  `SymbolNumber` int(11) NOT NULL auto_increment,
  `Symbol` varchar(30) NOT NULL,
  PRIMARY KEY  (`SymbolNumber`),
  KEY `Symbol` (`Symbol`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and the tick file looks like:

CREATE TABLE `ticks_20070912` (
  `SerialNumber` bigint(20) NOT NULL auto_increment,
  `Symbol` int(11) NOT NULL,
  `DateTime` datetime default NULL,
  `Price` int(11) default NULL,
  `PriceType` int(11) default NULL,
  `Size` int(11) default NULL,
  PRIMARY KEY  (`SerialNumber`,`Symbol`),
  UNIQUE KEY `Serial Number` (`SerialNumber`),
  KEY `Symbol` (`Symbol`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

There is a very, very small improvement in speed with the new format. selecting all of the AAPL ticks took 12.5 seconds the old way (...where symbol='AAPL'), and 12.3 seconds the new way (...where symbol=7287),  +/- my finger delay on the stopwatch.

And yes, I am only counting the time that it takes for MySQL to *start* returning data, not the actual time to transmit the thousands of rows.

This is a very simple query - SELECT * FROM ticks_20070912 WHERE symbol=7287; So I don't see OLAP helping things at all.

Also the file sizes did not change much, the index file actually got larger

Old index file - 441,556K
New index file 550,014K

Old data file - 474,437K
New data file 398,815K

I know I could compress the data files, and that would help some, but I need this to be MUCH faster.

So, now what am I going to do to speed this up?

The reason could be that you are selecting all rows. Try running queries like the following and compare the speed.

SELECT count(*) FROM ticks_20070912 WHERE symbol=7287;
or maybe
SELECT symbol, count(*) from ticks_20070912 group by symbol;

This should be significantly faster. Please send me an email as well.

OK, I did the tests you requested, and there really wasn't much difference. So, I got to thinking about compressing with myisampack. I had tried this a long time ago with pretty unimpressive results, but I figured I give it a shot after changing the varchar symbol column into an int.

Whoa! A query that ran for 12.5 seconds on the original file now takes 2.1 seconds! This is seriously looking good. I am now processing more days of raw data so I can do more in-depth testing. It takes about an hour to process each day's worth of ticks into the database, so I won't be able to try any of this till tomorrow. But I'm feeling optimistic.

BTW, Is there an easy way to time these things (easier, that is, than my stopwatch)?

That is really good news! Congratulations!

If you use the standard mysql client. /mysql/bin/mysql.exe it says how long each query took after it was executed.

I tried to get the same results compressing a table with 5 million rows containing only integer values, and it actually reduced the performance when I tried running similar select queries to what you did. Sometimes the performance was half...