Mysql speed optimization: 1 big table or many (>1000) tables?

Posted on 2006-03-29
Last Modified: 2009-12-16
i want to log the stock quotes for use in own program.
i log folowing data:
time - date - market - name - symbol - quote

numer of stock quotes: minimum 1000
number of new records a day : minimum: 150.000

Every minute i write about 100 - 200 new records
i never update records (i want to keep the old ones to see evolution)
i read the records of every quote ones a minute
i delete old quotes after a week (to save space)

what do i do:
1) i make 1 table and write every time: time - date - stockmarket - name - symbol - quote
2) i make 2 tables
   - 2.a: table with data:  market - name - symbol + ID
   - 2 b: table with data time - date - quote + ID
3) i make for every sock a new table: 1000 tables + 1 table with information: stockmarket + ID

What is the fastest way?

Question by:jodavid
    LVL 30

    Accepted Solution

    Option #2 - definately.
    LVL 33

    Assisted Solution

    I agree with todd_farmer - option 2 is the best.

    If you want to keep the old data, you might consider some of the archival type storage engines (ARCHIVE and packed MyISAM).

    You might also consider if you really need to keep every data value, or if it's sufficient to just keep the daily high, low, and average.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Creating and Managing Databases with phpMyAdmin in cPanel.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now