Re: Help with partitioning an existing table

[Ravi Malghan]

Rick: thanks for the detailed response. Here's some background and answer to your questions.

a. The rows that are stored in the database will always have unique SERVERSERIAL and SERVERNAME. So I have those in the primary key. SERVERSERIAL is a counter that incerements in the source application that is storing data in mysql.
b. I wanted to partition the data by each quarter (FIRSTOCCURRENCE is the time row is inserted) so added that to the primary key. If I have understood correctly, any field I want to partition by has to be in the primary key. I started inserting rows into this table beginning of this quarter and it has about 3 MIL rows. As per our companies policy, I need to keep data for 3 years. So about 36-40MIL rows is what I am expecting after 3 years. After which I can start purging data. So having partitioned by FIRSTOCCURRENCE will make it easy for me drop partitions every quarter after 3 years.

All my queries to this table include FIRSTOCCURRENCE in the where clause. So how I have indexed and partitioned definitely is helping me in terms of response time compared to a table with FIRSTOCCURRENCE not in the index.

Now, I have a new requirement from users that this response time is not fast enough. Their (application) queries always include customer name in the where clause. To address this,
a. should I just add the customer name to the primary key index
b. should the table be partitioned by firstoccurrence and customername so the queries look for rows in fewer partitions and hence get a faster response?
c. or should I just add index for the customer field

Thanks
Ravi