# Splitting Of Tables;

**URL:** <https://forums.percona.com/t/splitting-of-tables/238>\
**Category:** Other MySQL® Questions\
**Created:** [March 12, 2007, 5:50am UTC](https://forums.percona.com/t/splitting-of-tables/238 "2007-03-12T05:50:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![neopheonix](https://avatars.discourse-cdn.com/v4/letter/n/e68b1a/32.png) [@neopheonix](https://forums.percona.com/u/neopheonix)\
**Post date:** [March 12, 2007, 5:50am UTC](https://forums.percona.com/t/splitting-of-tables/238/1 "2007-03-12T05:50:02Z")

</div>

Hi,

I need some help for the Splitting of tables as i’m a newbie in this field.I’ve a running application which is inserting data to some reporting tables whenever a event is generated.For some reason we are facing issues with the table as we try to run few quries it locks down the tables and all the users got affected. As it is a running application i cannt do about the design of table we are using.I want to split the data into two tables refering the date in which data is added.I want to do it on the production sever so cannt take a risk of locking the table.I want to split the data lets say before 2007 to some seperate table.

Could you please suggest how to go about as i tried it with simple query where entry time \< ‘2007’ it is taking way too long which cannt be done on my server.We have about 1 million records in that table and 1 reference table which uses the same data from this table. I’ve to split both the tables.

I would really appreciate if u can help me in this.

Regards  
Neo Pheonix

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [March 12, 2007, 8:45am UTC](https://forums.percona.com/t/splitting-of-tables/238/2 "2007-03-12T08:45:45Z")

</div>

You should really be more specific what is structure what queries do you run what exactly happens which you do not want to happen.

---

<div class="post-metadata">

**Author:** ![neopheonix](https://avatars.discourse-cdn.com/v4/letter/n/e68b1a/32.png) [@neopheonix](https://forums.percona.com/u/neopheonix)\
**Post date:** [March 15, 2007, 7:31am UTC](https://forums.percona.com/t/splitting-of-tables/238/3 "2007-03-15T07:31:52Z")

</div>

Hi,

Thanx for replying.

Here are the 2 tables i want to split which are interlinked by OP\_ID.

Table\_1

±--------------------±-------------±-----±----±-------- ±------+  
| Field | Type | Null | Key | Default | Extra |  
±--------------------±-------------±-----±----±-------- ±------+  
| OP\_ID | int(10) | | | 0 | |  
| NUMBER | varchar(20) | YES | | NULL | |  
| SET | varchar(50) | YES | | NULL | |  
| SESSION\_ID | varchar(100) | YES | | NULL | |  
| EVENT\_NUM | int(4) | YES | | NULL | |  
| REPEAT\_NUM | int(4) | YES | | NULL | |  
| OP\_COUNT\_IN\_SESSION | int(4) | YES | | NULL | |  
| APP\_ID | varchar(25) | YES | | NULL | |  
| CONTAINER | varchar(25) | YES | | NULL | |  
| OP\_TYPE\_ID | int(3) | YES | | NULL | |  
| SETKU | varchar(50) | YES | | NULL | |  
| IP\_ADDRESS | varchar(25) | YES | | NULL | |  
| RST\_TO\_CLIENT | varchar(10) | YES | | NULL | |  
| SERVICE\_RST | varchar(10) | YES | | NULL | |  
| OP\_START\_TIME | datetime | YES | | NULL | |  
| OP\_END\_TIME | datetime | YES | | NULL | |  
| SERVICE\_START\_TIME | datetime | YES | | NULL | |  
| SERVICE\_END\_TIME | datetime | YES | | NULL | |  
| ENTRY\_TIME | datetime | YES | | NULL | |  
| AFFILIATE\_TAG | varchar(10) | YES | | NULL | |  
±--------------------±-------------±-----±----±-------- ±------+

Table\_2

±------------±------------±-----±----±--------±------+  
| Field | Type | Null | Key | Default | Extra |  
±------------±------------±-----±----±--------±------+  
| OP\_ID | int(10) | YES | | NULL | |  
| PARAM\_NAME | varchar(50) | YES | | NULL | |  
| PARAM\_VALUE | varchar(50) | YES | | NULL | |  
±------------±------------±-----±----±--------±------+

As this is a part of the running application as soon as i split this table using the below queries the table got locked and no insertion and select is executed and its taking a long time so we cann’t pull the application off for such a long time duration.

Create Table Table\_1\_OLD as select \* from Table\_1 where date(ENTRY\_TIME) \<‘2007-01-01’;

Create Table table\_2\_OLD as Select A.\* from Table\_2 A, Table\_1\_OLD B where A.OP\_ID=B.OP\_ID;

Uptill this point everything is ok as i’m creating a new table having the data older then 2007 to some other tables.

But as soon as i try to delete the data from these tables Table\_1 and Table\_2 it get locked up and no insertion can be made on this causing a problem.

delete from Table\_1 where date (ENTRY\_TIME) \< ‘2007-01-01’;

delete from Table\_2 where OP\_ID \<= (select max(OP\_ID) from Table\_1\_OLD);

I hope everything is clear now. If you still have any doubt please let me know and also suggest some effective solution for this.

Regards  
Neo Pheonix

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 15, 2007, 9:48am UTC](https://forums.percona.com/t/splitting-of-tables/238/4 "2007-03-15T09:48:08Z")

</div>

First of all, do you _really_ need to split the tables?

Are you experiencing performance problems or do you just want to clean out old records?

I don’t know how many of your one million rows that are going to be deleted by this operation.  
But either way you look at this you will have to accept that deleting a lot of rows will take time.

I’m assuming that you have an index on OP\_ID since it looks like it is the primary key.  
But what you should know for example is that when you create a table with:  
CREATE TABLE Table\_1\_OLD SELECT \* from Table\_1  
Is that _no_ indexes are created.  
So your query:  
select max(OP\_ID) from Table\_1\_OLD;  
will have to make a table scan to find the highest OP\_ID.

So I suggest that you find out the highest OP\_ID first and then you use that in both your DELETE queries.

Also depending on the amount of rows that you are moving it is sometimes better to:  
stop the application  
rename the table to old\_table.  
create a new table.  
insert the records that you want to keep into the new table.