SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot auth1's Avatar
    Join Date
    Nov 2004
    Location
    Melbourne, Australia
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Eventual performance of large table (many million rows)

    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:

    Code SQL:
    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:

    Code SQL:
    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. #2
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by auth1 View Post
    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?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •