0

I have been reading about the table partitioning in mariadb.

I know it will not give any direct performance gains. But what about the index tables? Are they also partitioned?

Let's say a table is partioned by user_id so all data related to one user will be in the same table partition. In a user session each query will therefore only fetch data from a single table partition

My question is: Will the index tables also be partitioned accordingly? When a query is executed it's only searching in the partitionend index table and not the entire = less indexes to search

Improve this question
2

2 Answers 2

Reset to default
2

Think of it this way... A Partitioned table is a set of separate tables, based on the partition key. Each "sub-table" has the partition's data in a BTree, plus separate BTree for each secondary indexes. The sub-tables (partitions) are independent of each other. (Often the trade-off is a wash.)

Case 1: If "partition pruning" decides that only one partition applies, then only one sub-table will be looked in. In this case, there is a tradeoff between picking the partition versus slightly more work in a single index that might be required if the table were not partitioned.

Case 2: If pruning does not apply, then each sub-table is searched (via one of its indexes, if appropriate) for the row(s) desired. Then, the results from each sub-table are combined (a la UNION). This is likely to be slow -- because of "opening" each partition and looking into an index in each. (O(Np) for looking in all Np partitions, then O(log Nr) to drill down each of Np indexes.

More discussion: Partition

Improve this answer
2
  • What do you mean by: Often the trade-off is a wash ?
    – clarkk
    Nov 14 at 11:37
  • @clarkk - Very little performance gain or loss -- when comparing partition pruning plus index, versus all the work being done in the index. Do note that the optimal index(es) for a partitioned table versus the equivalent non-partitioned table are likely to be different.
    – Rick James
    Nov 15 at 17:11
0

My question is: Will the index tables also be partitioned accordingly?

Yes, indexes "partition" the data in an exponential way when compared to regular partitioning which only divides the data linearly. Indexes have a search time complexity of O(log(n)), partitioning is O(n). Indexes are meant to improve the performance of queries, partitioning not so much. Rather, partitioning is meant for improved data management, such as dropping only a subset of the data that's not longer needed at a time.

When a query is executed it's only searching in the partitionend index table and not the entire = less indexes to search

Yes, as long as your index is properly designed for the query it's being used for, and you're getting index seeks against it, then it'll only need to search the part of the index relevant to your query, efficiently.

Improve this answer
4
  • ok, so what you are saying is that seeking in indexes in a partitioned table takes longer if you have to search across multiple table partitions? But if the data you are searching for (the user id) is in the same table partition the seek time is still O(n)? Only if you have partitioned the data (user id) across multiple partitions it's O(log(n)) ?
    – clarkk
    Nov 12 at 16:41
  • @clarkk I did not comment on anything specific to index seek performance when it's across multiple partitions or not (though it's probably a negligible difference, as long as you're getting an index seek). My points are just that partitioning is not meant for improving performance of search queries but indexing is. Partitioning only divides the data linearly, whereas indexing divides the data logarithmically which is exponentially more efficient when searching.
    – J.D.
    Nov 12 at 18:11
  • But I already state in my question that partitioning is not for performance
    – clarkk
    Nov 14 at 18:53
  • @clarkk But you are asking about performance here "When a query is executed it's only searching in the partitionend index table and not the entire = less indexes to search" and here "so what you are saying is that seeking in indexes in a partitioned table takes longer if you have to search across multiple table partitions?". It wasn't clear what you meant by "index tables" in regards to partitioning, so I answered assuming you were asking how indexing works vs partitioning. An index and a table are different concepts.
    – J.D.
    Nov 14 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.