MySQL Forums
Forum List  »  Partitioning

Re: Open tables with Partitioning
Posted by: Rick James
Date: March 02, 2011 11:29AM

Aside from the cost of opening whatever, there are potentially significant differences in what INDEXes work in PARTITION versus non-PARTITION. The performance differences there could overwhelm the "opening" costs. So, let's see
SHOW CREATE TABLE (with and without partitioning)
SELECTs (representative ones with representative WHERE clauses)

Based on what little you have said, it may be a tradeoff between
    USE $name
    SELECT ... WHERE key = $key
    INDEX(key)
versus (for PARTITIONed)
    SELECT ... WHERE name = $name AND key = $key
    INDEX(name, key)

Notice that the PARTITION may need a compound key, which may (or may not) change the dynamics of it. The choice of what to "partition on" can make a big difference. If you simply partition `name`, you are getting none of the other benefits of PARTITIONing, and yes, the question mostly boils down to "opening costs".

Options: ReplyQuote


Subject
Views
Written By
Posted
4401
February 28, 2011 04:56AM
1631
February 28, 2011 10:03AM
2151
March 01, 2011 11:22PM
Re: Open tables with Partitioning
1506
March 02, 2011 11:29AM


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.