MySQL: Large table splitting

Question

I have a huge table in a database and I want to split that into several parts physically, maintaining the database scheme.

For example, the table name is TableName and has 2 000 000 rows.

I would like to split that table into four parts, but I want to work in the same way with the table, so

select [Column List] from TableName where [Filter]

insert into TableName ([Column List]) values([Values])

update TableName [Updates] where [Filter]

delete from TableName where [filter]

would work in the same way after splitting the table as before. Basically I want my database to handle in different threads my queries. How can I achieve this?

Thanks in advance.

As mentioned by Hammerite partitioning would be much better solution. However, 2 000 000 is not all that much and with good indexes and well designed queries shouldn't work slow at all. — Mchl
– Mchl, Commented Sep 20, 2010 at 12:50
The problem is that several selects are done by an authomatic project which is running on five threads on each of several servers and the table has a lot of columns too. Sometimes it takes ages to do some simple commands in the database. So we are not talking about running a select query, we are talking about running dozens of queries per second or even more, copying the data to a slave duplicae which is shown on a website. So, in our case, partitioning is the only option. The table is indexed. — Lajos Arpad
– Lajos Arpad, Commented Sep 20, 2010 at 13:05

Hammerite · Accepted Answer · 2010-09-20 12:47:14Z

5

Perhaps you should look at partitioning.

answered Sep 20, 2010 at 12:47

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vishva8kumara · Accepted Answer · 2017-03-03 19:48:16Z

1

If you are looking into copying data to a separate slave / duplicate; consider implementing that by binary logging, so the replica will read the binary logs to do the replication, rather than doing it manually or programmatically.

answered Mar 3, 2017 at 19:48

vishva8kumara

3551 gold badge3 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL: Large table splitting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related