Hi,
Anthony Willard wrote:
> I've seen many examples of how partitioning helps
> scanning of many millions of rows of data. What
> if your data is indexed? Does partitioning
> actually increase performance over non-partitioned
> tables with good keys and SQL?
>
It can improve performance slightly since it divides the indexes and
thus each index is less levels. However this benefit mainly comes to
queries that goes against a single partition. For queries against an
index on all partitions it can even slow down performance.
In a sense one could view partitions as a form of index, it splits the data
in buckets and thus if you know which bucket you're using there is less
data to wade through.
> I have a claims table in my warehouse that
> contains more than 6 million data rows. Query
> performance is good (once the keys are cached,
> even better ;-) ).
>
> The table has a primary key on Claim Number and
> Claim Line Number. I was considering partitioning
> by Paid Date, but that column is not contained in
> the PK. And, from my exploration of this new
> feature, I'm not sure I can partition my table.
> Can I?
>
Not with partitioning as in 5.1, in later releases this limitation will be
removed.
> Assuming I can partition my claim table, would I
> get parallel query execution if a query spanned
> multiple partitions and thereby a performance
> improvement?
>
No parallel query execution in 5.1, it is in the plans though.
Rgrds Mikael
> Any help in understanding is appreciated.
>
> Anthony
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com