ScaleDB for MySQL, A Multi-Table Index Technology
ScaleDB’s Multi-Table Index (MTI) provides a significant boost to database performance and functionality. ScaleDB MTI is a general purpose index—meaning it isn’t just for special cases that delivers advantages not just in one area, but across the board in simple queries, joins, updates and inserts. While traditional B-Tree indexes only maintain information about the data in tables, ScaleDB indexes both the data in the tables and the relationships between those tables. Yet because it is stored in a very compressed structure, typically 25% of the size of a comparable B-tree index, it is also extremely fast. Not only does ScaleDB improve performance, it also delivers additional flexibility to application developers. For these reasons, ScaleDB’s MTI delivers asignificant boost in database performance.
Compressed Index Delivers Superior Performance
The computer’s disk is the slowest component in the server. For this reason, a key tactic for improving database performance is minimizing disk access. The indexes in enterprise databases typically overflow memory, with the excess being stored on disk. An index with a smaller footprint can store more of that index in faster memory, versus storing overflow on a slower disk. Because ScaleDB’s MTI is typically 25% of the size of a comparable B-tree index, you can fit 4X as much of the index in memory. This results in faster performance. ScaleDB’s MTI is a three-level hierarchy, with the first two levels consuming very little memory. As a result, even the largest databases will only require a single disk I/O to read the third level. B-tree, on the other hand can have multiple layers, resulting in several disk I/Os to satisfy a single database request. This delivers a further performance advantage.
Eliminates the Join Penalty
Using traditional indexes to join two or more tables, in order to satisfy a query, is a very expensive process. It involves serialized steps, reading the index and then reading the data for each table in the join. This serialization is what makes joins slow in traditional databases. There are two ways to eliminate the join penalty with B-tree: (1) merge the smaller modular tables into one oversized table; (2) maintain the separate tables, but create another table that contains a copy of that data in one oversized table, and synchronizes that data with the separate tables. The later method is called a materialized view.Unfortunately, both of these approaches create a maintenance headache. ScaleDB’s MTI implements a third approach. Since the MTI understands the relationships between the tables, it can combine the data on the fly. In other words, ScaleDB performs multi-table joins at the speed of reading a single table. You get the superior performance of materialized views, without the issues of data synchronization and inconsistent data.
Increased Developer Flexibility
Developers prefer to keep their data in smaller, more modular tables. This approach results in simplified manipulation, maintenance and also a smaller database size. Database administrators (DBAs) require larger tables, in an effort to minimize or eliminate the join penalty. By removing the join penalty, ScaleDB MTI enables developers to use a modular design, while DBAs get the performance of a single table. All of this is delivered without any change to your application, by simply plugging in the storage engine under MySQL.
Faster Inserts on Foreign Keys
Using a traditional B-tree index, an insert on a field that is a foreign key requires a query of the primary key to ensure that you are maintaining referential integrity. Because the ScaleDB MTI inherently maintains the relationship information, it eliminates the need to query the primary key in this scenario. This results in faster inserts on foreign keys.
Support for Longer Keys
ScaleDB’s MTI provides support for longer keys without impacting performance. As more companies are indexing on very longs keys (e.g. URLs) this capability becomes increasingly important.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.