MySQL Forums
Forum List  »  InnoDB

Understanding MySQL indexes
Posted by: SIDDHARTH jain
Date: October 23, 2022 01:09PM

Is it correct that when we create an index in InnoDB, it indexes every value in the column being indexed? Suppose we have a table with 2 billion rows in it. Then the index will have approx. 2 billion records in it. IMO this prevents InnoDB from scaling to tables with billions of rows. OLAP databases like ClickHouse take care of this by indexing not every record, but group of records. ClickHouse calls it a granule. By default a granule contains 8,192 rows in it. So now instead of storing 2 billion values in the index, it has to store only 244,140 values. This can easily fit into the machine's RAM. It would be good to have a setting in InnoDB where we can have this kind of behavior (N=8192 vs N=1 - the default)

Options: ReplyQuote


Subject
Views
Written By
Posted
Understanding MySQL indexes
61
October 23, 2022 01:09PM


Sorry, only registered users may post in this forum.

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.