Eventual performance of large table (many million rows)

Databases
1

Hi,

After some reading Ive moved away from individual tables and I m importing my end of day stock data (some 4000 rows) into a single table. Currently my database structure looks like this:


CREATE TABLE IF NOT EXISTS `shares` (
  `id` mediumint(7) NOT NULL auto_increment,
  `name` char(8) NOT NULL,
  `date` date NOT NULL,
  `open` decimal(9,3) unsigned NOT NULL,
  `high` decimal(9,3) unsigned NOT NULL,
  `low` decimal(9,3) unsigned NOT NULL,
  `close` decimal(9,3) unsigned NOT NULL,
  `volume` mediumint(7) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `date` (`date`),
  KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

The table is fast approaching 500,000 rows, which has me thinking about the performance of the table at 10’s of millions of rows.

Is there any stage that I should move (now or later) to a structure like this, and create many thousand of tables:


CREATE TABLE IF NOT EXISTS `name_of_share` (
  `id` mediumint(7) NOT NULL auto_increment,
  `date` date NOT NULL,
  `open` decimal(9,3) unsigned NOT NULL,
  `high` decimal(9,3) unsigned NOT NULL,
  `low` decimal(9,3) unsigned NOT NULL,
  `close` decimal(9,3) unsigned NOT NULL,
  `volume` mediumint(7) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `date` (`date`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

It has been some 3 months to accumulate half a million rows and I would like (hopefully) to stay in the business for 5 - 10 years, at which point the database will be quite large.

When I query, I only look at one stock at a time, and I never access two stocks at one time. With this in mind, would it be quicker to query individual tables, eventually?

2

You should read this article: Improving Database Performance with Partitioning

You will notice the differences between counting 8,000,000 rows in a non-partitioned table and a partitioned one:

non-partitioned: 38.30 seconds
partitioned: 3,88 seconds

Of course those values depend on the server’s hardware as well. But the point is you can save a lot of time with partitioned tables if you have such a large amount of data you want to handle.

P.S. Also read Restrictions and Limitations on Partitioning

3

with that in mind, you need only one table

the number of rows means nothing to a performance question

the existence of the appropriate index(es) for your query is what matters

:slight_smile:

4

The way your database will behave largely depends on the hardware too.
Partitioning is a great way to improve performance, however, logic implies - the better the hardware, the better performance.
With that in mind, InnoDB has several configuration options that can make it fly on a robust machine.

Having in mind what kneekoo and Rudy said, bear in mind that you can configure InnoDB to scale better since default options aren’t that good for larger scale data retrieval.
I’m sure you’ll be able to google out these config options of InnoDB :slight_smile: