CODEWITHSUNDEEP
mysql
Fernando
Fernando

Reputation: 4629

Handling millions of rows in single or multiple tables?

I am looking for some experiences from people that worked with big databases ( > 100GB ).

Basically I have a table that stores all messages sent by a system, for all accounts. Every week, we get aprox. +1 million new rows.

This table is starting to give problems with some queries. I understand how to proper optimize queries and tried different combinations of indexes. But i think that it's just too much for one table.

One of the queries, which uses a range condition is what usually kills our database.

From your experience, is it better to split this table into several tables ( one for each account )? Each account already has its own database, so it's not hard to modify our software for this.

I guess this would solve our problem with ranged queries, specially for small accounts with < 5k rows.

Upvotes: 1

Views: 529

Answers (4)

Suroot
Suroot

Reputation: 4423

I don't quite understand though, you say that each account has its own database but that you are looking at splitting by account into separate tables?

Another option, since I haven't seen anyone mention it, is sharding. Although this is a great technology and if used properly can help to scale; there become issues with response times as you shard more and more. Also, depending on how much data is stored per tuple (per row without any joins), you might be able to split up the fields into separate tables using normalization techniques. Not sure how far along your database is for that, but just a suggestion.

Upvotes: 0

user149341
user149341

Reputation:

Manually partitioning tables in MySQL is a bit of a minefield. In general, unless you can split your data into multiple tables which will contain different types of data (i.e, tables which will have a substantially different set of columns), you're generally better off adding indexes to make your queries run faster. The exception is when your data set has truly grown large enough that it's too much for a single database, at which point sharding (across servers) will help.

Upvotes: 0

Icemanind
Icemanind

Reputation: 48686

I haven't worked with MySQL directly. I'm more of a SQL Server and Oracle guy. But I do know that MySQL has table partitioning, which breaks big tables and distributes them across files. You can set rules to the partitioning and how it all works.

Another tip you can try to do, is to separate your data into an archive table. For example, in your back end, if you need to search for messages by date and if the date range is over a certain amount of time, then your back end does its search in the archive table. Otherwise, it searches in the main table. This will allow you to maybe archive older messages.

Upvotes: 1

user618788
user618788

Reputation:

MySQL is generally faster handling multiple tables rather than one single table. If you can split the data into sections (users in one table, actions for each user in another table, etc), it will be generally faster. It's very important to note that if you decide to split data into multiple tables, you have to connect them all together with IDs.

Some useful information: http://dev.mysql.com/doc/refman/5.1/en/multiple-tables.html

Upvotes: 0

Related Questions