Database optimization improves efficiency and responsiveness while preserving data consistency and integrity. It balances performance, storage efficiency, maintainability, security, and scalability. section 3.2 section 7.1
Use a practical optimization workflow:
- Understand application-generated queries, user interactions, and observed performance bottlenecks. section 7.10
- Use query logs and production performance metrics to identify indexing opportunities. section 7.10
- Add indexes that match frequent searches and filters, then check that schema changes do not harm performance or consistency. section 7.10
- Consider denormalization when reducing query cost justifies storing some information redundantly. section 7.0
- Replace unwieldy validity checks with reference tables when a controlled list contains many valid values. section 8.12
Indexing common product interactions
Suppose users search products by name and frequently view reviews. A full-text index on product.name supports keyword searches; an index on review.code supports lookups such as product code 23356. Similar reasoning adds an index on payment_method.email when email lookups are frequent. section 7.10
-- comment: works for MySQL and MariaDB -- comment: consult ChatGPT on adaptations -- comment: for other RDBMS CREATE FULLTEXT INDEX ft_idx_name ON product (name);
Shows the SQL pattern for creating a MySQL or MariaDB full-text index on product.name. section 7.10
