Database Indexing and B+ Trees

B+ Trees and Indexes in MySQL

• B+ trees are the underlying data structure of most indexes in MySQL.
• A secondary key in MySQL is any key (or index) that is not the primary key, but has a special relationship to the primary key.

Creating Indexes

• Indexes can be created on a single column or across multiple columns, with the latter showing more power in indexing.
• Indexing only a portion of a column can lead to smaller indexes with no loss in selectivity, depending on the data.
• A covering index is a regular index in a special situation, and is perhaps the fastest way to satisfy a query.
• Indexes can be created on a function, which is helpful when indexing something that can't quite be reached.
• It is possible to index particular keys in JSON columns, as indexing an entire JSON column is not possible in MySQL.

Characteristics of Indexes

• Indexes maintain a copy of part of the data, with each index having a pointer back to the row.
• Creating multiple indexes means having multiple copies of different parts of the data.
• The key to creating indexes is to strike a balance between creating as many as needed and as few as possible, considering factors like database size, update frequency, and performance impact.

Importance of Indexes

• Indexes are ten times more important than schema design for performance, and maybe even a hundred times more important.
• Indexing is crucial for a high-performing application, as it ensures queries perform well.

Dropping Indexes and Full-Text Searching

• Dropping an index can be scary, but making it invisible first can help test the ramifications on the application.
• FULLTEXT indexes can be used to search across multiple columns, unlocking new search capabilities and optimizing databases.
• Mastering full-text searching with MySQL can be beneficial.

Foreign Keys and Composite Indexes

• Foreign keys and foreign key constraints are related but not the same.
• Composite indexes can lead to redundant indexes, which can occur when some indexes are not used.

Introduction to Indexing

• Indexing is the best way to ensure query performance, which is essential for a high-performing application.
• Understanding access patterns is crucial when deciding which indexes to create.

Learn about B+ trees, secondary keys, and how to determine where to add indexes in MySQL. Understand the importance of index selectivity and how to create effective indexes.