Re: Help with partitioning an existing table
Posted by: Rick James
Date: December 21, 2012 05:42PM

> will always have unique SERVERSERIAL and SERVERNAME
By that, you mean the 'pair' of fields is unique? Or each field is independently? (I will assume the former.) But even so, the "uniqueness" constraint is lost because of having to add `firstoccurrence`. So... Is there really any need to index either or both of SERVERSERIAL and SERVERNAME?

> a. should I just add the customer name to the primary key index
No.

> 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?
No.

> c. or should I just add index for the customer field
Yes.
Is it 'unique'?

Recommend...
INDEX(CUSTOMER)
PARTITION BY RANGE (firstoccurrence)
(but I don't know about SERVER*)

Here's some of the logic...
A SELECT (or UPDATE or DELETE) will
1. Do "partition pruning" based on firstoccurrence (IF that is part of the WHERE clause)
2. Use one INDEX (keep in mind that a 'PRIMARY KEY' is an INDEX) to further be selective. So, we need to have the first field(s) of some INDEX coming from the WHERE clause.

So, IF you _always_ have
WHERE firstoccurrence BETWEEN ... AND ...
AND customer = '...'
then my recommendation is good.

As a side note, this would be equally good:

INDEX(customer, firstoccurrence) -- in that order
and NO partitioning.
(However, the purging after 3 years says that PARTITIONing is really worth doing, so keep the PARTITIONing.)

Note that I said "BETWEEN" and "=". If you are using "LIKE" or other things, my comments may be invalid. So, I really need to see a sampling of the SELECTs.

Tentatively, we are back to your original posting, with the addition of
INDEX(customer)

Oh, plus you need to fix the partition boundaries.

> SERVERSERIAL is a counter that incerements in the source application that is storing data in mysql.
Be careful -- If you have multiple threads doing this, they could step on each other. Have you dealt with the concurrency issues? Perhaps: Use InnoDB and BEGIN...COMMIT. And realize that there can be cases where a SERIAL number is 'burned'--that is, lost due to a rollback or other hiccup.

Options: ReplyQuote


Subject
Written By
Posted
Re: Help with partitioning an existing table
December 21, 2012 05:42PM


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.