Re: Modify primary key for partitioning
Posted by:
Aftab Khan
Date: August 22, 2012 10:16AM
>`ID_MSG` varchar(80) CHARACTER SET latin1 NOT NULL
>PRIMARY KEY (`ID_MSG`
`ID` UNSIGNED INT NOT NULL is more appropriate, as it would help reduce length of secondary keys
>(I'm not sure the PK is used if ID_MSG is the first column in the index)
>(`ID_MSG`,`RECEIVED_DATE`)
If you show us SELECTs then we would be able to assist, however, the above composite index would be used in following cases:
WHERE ID_MSG='S'
WHERE ID_MSG='S' AND RECEIVED_DATE BETWEEN 'S' AND 'S'
Index won't be used when:
WHERE RECEIVED_DATE BETWEEN 'S' AND 'S'
>There are currently 300,000 rows in the table
>This table is mostly used with batchs. Around 15,000 inserts per Month this year, but it's gonna increase to 60,000 per Month shortly
So, in four years time you would have approx 2.8 million rows in this table.
How much RAM do you have on the server? assuming at least 8GB or more.
By looking at numbers you have provided. I won't recommend using partitioning
Subject
Views
Written By
Posted
2923
August 10, 2012 10:29AM
1605
August 18, 2012 09:39PM
1875
August 20, 2012 06:33AM
Re: Modify primary key for partitioning
1636
August 22, 2012 10:16AM
1692
August 23, 2012 09:16AM
1609
August 23, 2012 10:42AM
1618
August 28, 2012 10:14AM
1567
August 30, 2012 10:26PM
2023
August 31, 2012 03:05AM
1595
August 31, 2012 03:38AM
1550
August 31, 2012 06:48AM
1667
August 31, 2012 09:39AM
1528
September 04, 2012 01:02AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.