Splitting Of Tables;

MySQL & MariaDB Other MySQL® Questions
1

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

2

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

3

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

4

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.