Database management and design

Welcome to the database management and design documentation. This section provides resources and guidelines for managing and optimising our databases effectively.

Database Emergency Response Plan (DERP)

Our Database Emergency Response Plan outlines the steps to prevent and respond to database performance issues. It is a living document that we continuously update based on our experiences.

Database design and optimisation

Optimising queries

Learn how to optimise queries to improve database performance. This includes using EXPLAIN ANALYSE, adding indices, and reworking application logic.

Indexing best practices

Proper indexing is crucial for maintaining database performance and ensuring efficient data retrieval. This document outlines best practices for creating and managing indexes in your database.

General guidelines

  1. Understand your queries: Analyze the queries that are frequently run against your database. Indexes should be created based on the columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
  2. Use EXPLAIN: Utilise the EXPLAIN command to understand how your queries are executed and identify which indexes are being used.
  3. Avoid over-indexing: While indexes can improve read performance, they can also slow down write operations. Only create indexes that are necessary for your queries.
  4. Index selective columns: Index columns that have a high degree of uniqueness. Indexes on columns with many duplicate values are less effective.
  5. Composite indexes: Use composite indexes (indexes on multiple columns) for queries that filter on multiple columns. The order of columns in the index should match the order in the query.
  6. Covering indexes: Create covering indexes that include all the columns needed by a query to avoid accessing the table data.
  7. Monitor and maintain: Regularly monitor the performance of your indexes and remove any that are not being used. Rebuild or reorganise indexes periodically to maintain their efficiency.

Creating indexes

Single-column index

CREATE INDEX idx_column_name ON table_name(column_name);

Composite index

Composite indexes are useful for queries that filter on multiple columns. The order of columns in the index should match the order in the query; if the leading column in the index is not being queried, the index will not be used.

CREATE INDEX idx_column1_column2 ON table_name(column1, column2);