Database Indexing
[[Learning/Database/Database Indexing|Database Indexing]] is a crucial technique used to enhance the speed of data retrieval operations in a database system. It works similarly to an index in a book, allowing the database engine to quickly locate the data without scanning every row in a table, thereby reducing the amount of time and resources needed for queries.
Key Points:
How Indexing Works: An index creates an entry/Hash value for each value in a database table's column, making it faster to find rows matching a query condition. The index is stored separately from the table data, allowing for efficient searches.
Types of Indexes:
Single-column Indexes: Created on only one column of a table.
Composite Indexes: Involve multiple columns.
Unique Indexes: Ensure that all values in the index are distinct.
Full-text Indexes: Designed for text-based columns to facilitate full-text searches.
Advantages:
Increased Speed: Particularly beneficial for large databases, indexes significantly reduce query time.
Improved Efficiency: With faster data retrieval, overall system performance is enhanced.
Orderly Data Retrieval: Some types of indexes, like B-trees, keep data sorted, aiding in orderly data retrieval.
Considerations in Using Indexes:
Storage Space: Indexes consume additional disk space.
Maintenance Cost: Inserting, updating, or deleting rows in a table can be slower because the indexes also need to be updated.
Choosing the Right Columns: Not all columns are good candidates for indexing. Frequently searched columns, columns with a wide range of values, and columns used in JOIN operations are typically good choices.
Implementation:
Creating Indexes: Done using SQL commands, like
CREATE INDEX.Index Algorithms: Common algorithms include B-tree, hash, and R-tree, each suitable for different types of queries and data.
Performance Considerations: Proper indexing strategies depend on understanding the database schema, the nature of the queries performed, and the specific database system being used.
Conclusion:
Effective use of database indexing is a balance between accelerating data retrieval and managing the extra resources required for maintaining the indexes. While it's a powerful tool for improving database performance, careful planning and ongoing management are essential to reap its benefits.
process for implementing indexing:
run : ANALYZE TABLE quotes;then check the index available:
SHOW INDEX FROM quotes;run a base query: do a select query and record its rows(use EXPLAIN) and response time(use the response time)
create the index:
CREATE INDEX index_name ON table_name (column1, column2, ..., columnN);run the base query again and check if there is any response time improvement
if not remove the index:
DROP INDEX index_name;else.. yeay.. we improved the speed.
INDEX naming
name index in the format of
currently I name things with a prefix of idx with a follow up of tablename_column_name
#flashcards #database What is the command to run before checking for index in SQL::ANALYZE TABLE quotes;
What is the command to check for index in a table::SHOW INDEX FROM quotes;
What is the command to add an index to column::CREATE INDEX index_name ON table_name (column1, column2, ..., columnN);
How to check performance of a query::Put EXPLAIN before the query
How to remove an index::DROP INDEX index_name;
Last updated