SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Huge Table

  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Location
    India
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Huge Table

    Hi Guys,

    I am working on a project in which an admin uploads 4000-5000 records per day in a table through CSV files (average 500 records per CSV upload). Database is MYSQL.

    Users on the main website could search and fetch data from this huge table.

    Now the table already has nearly half a million records and search and query functionality (on the main website) have become very slow. Though, I have applied a few indexes on this table and all other tables that are related to this table (through foreign keys) bur still the searches are slow.

    I could add some more indexes on this table however I am afraid that could slow down the inserts/updates (via CSV upload). As you know indexes slow down the inserts/updates.

    Do you guys have any idea regarding how to manage a database with such a huge table by optimizing both the processes (inserts/updates and searches).

    I look forward to your replies. Thanks.

  2. #2
    SitePoint Addict
    Join Date
    Sep 2007
    Posts
    371
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't think half a million records are much, in fact the site in my signature has a table with 2.6 million records (ip address mapping to location id). Are you sure you have created all the indexes properly which can be used to speed up your queries?

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Finland
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Viv5,
    I’m building large scale DB system in MySQL and I’ve tested table with 100 million records (size 4 GB). Test select that gets 700000 rows takes 8 seconds and total number of records doesn’t make it any slower.

    It all about index and order. But index in all key fields that you use in query and If you have ORDER BY “something” in SELECT query, try to leave that off.

    You can make INSERT much faster using following multi insert syntax:

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

    Read more http://dev.mysql.com/doc/refman/5.0/en/insert.html

    Multi insert syntax is faster because MySQL doesn’t have to sort index table so often.

    I’m inserting data in 1000 rows / INSERT clause.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by viv5 View Post
    Do you guys have any idea regarding how to manage a database with such a huge table by optimizing both the processes (inserts/updates and searches).
    at this point, you probably need to look at your mysql server configuration to make sure it's optimized for the number of records you have and the size of your indexes.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The size of your dataset is very small, simply ensure you index your queries correctly and that your queries are correct, i've had 1800 queries a second on some v.large databases and tables i've worked with. Bare in mind one bad query can grind your database server to a halt.

    To optimize your server checkout the following link and get used to EXPLAIN statement:
    http://hackmysql.com/mysqlreport

    Also 'watch -n0.5 "mysqladmin processlist" ' is very handy to see what your database is doing.

    I've put a few more tips on my blog that might help out: MySQL & PHP Performance Optimization Tips

    An example that I had yesterday of a bad query is that a colleague of mine, missed out a condition joining a users table to another, which caused a tmp table. This was generating 40gb temp files each and consuming all disk space, totaling around 200+gb.

  6. #6
    SitePoint Addict
    Join Date
    Mar 2005
    Location
    India
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys, thanks for the information. I am glad to learn that even tables with 1 billion records could be optimized for both INSERTs and SELECTs. I would need to optimize my table using Indexes sensibly. Thanks again.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    at the 1 billion level, a properly configured server is just as important as proper indexes.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •