Huge Table

[viv5]

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.

[dman_2007]

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?

[tjalonen]

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.

[longneck]

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.

[Andrew-J2000]

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.

[viv5]

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.

[longneck]

at the 1 billion level, a properly configured server is just as important as proper indexes.