MySQL Forums
Forum List  »  Partitioning

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Modify primary key for partitioning
1385
August 22, 2012 10:16AM


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.