Checking Query Execution Cost

To evaluate the efficiency of a query execution plan, inspect the optimizer's cost calculation.

SHOW SESSION STATUS LIKE 'Last_query_cost';

Prefix Indexing for String Columns

Indexing long text columns entirely can consume excessive space. Instead, index a prefix of the string that offers high selectivity. For instance, on a product_catalog table, we can calculate the selectivity of the full product_sku versus its prefixes.

-- Calculate selectivity of the full column
SELECT COUNT(DISTINCT product_sku) / COUNT(*) AS selectivity_ratio
FROM product_catalog;

-- Calculate selectivity of the first 7 characters
SELECT COUNT(DISTINCT LEFT(product_sku, 7)) / COUNT(*) AS prefix_selectivity_ratio
FROM product_catalog;

If the prefix selectivity is close to the full column selectivity, a prefix index is sufficient.

CREATE INDEX idx_sku_prefix ON product_catalog (product_sku(7));

InnoDB Clustered Indexes

InnoDB uses clustered indexes where the primary key determines the physical order of data. The leaf nodes of the primary index contain the entire data row. Secondary indexes store the primary key value as a pointer to the row. Consequently, keeping the primary key compact is critical to avoiding bloat in secondary indexes.

Compressed (Prefix Compressed) Indexes

Some storage engines compress index blocks to save space. For example, if sequential entries are 'optimization' and 'optimizer', the second entry might be stored as '11,er', representing the shared prefix length and the suffix.

Avoiding Redundant Indexes

Creating duplicate indexes wastes memory and write resources. For example, if a composite index exists on (department_id, status), a separate index on (department_id) is redundant because the composite index can already satisfy queries filtering on just department_id.

CREATE TABLE employee_records (
    id INT PRIMARY KEY,
    department_id INT,
    status VARCHAR(20),
    INDEX idx_dept_status (department_id, status),
    INDEX idx_dept (department_id) -- This is redundant
);

Optimizing Low-Cardinality Columns

For columns with a limited set of values (low cardinality), using an IN list allows the optimizer to efficiently use range scans or index lookups.

SELECT * FROM user_accounts 
WHERE account_status IN ('active', 'pending') 
AND last_login > '2023-01-01';

Pagination Optimization

Deep pagination with large offsets (e.g., LIMIT 10000, 10) is inefficient because the database must scan and discard the first 10,000 rows. A more efficient approach is a "deferred join" or seeking specific positions.

-- Inefficient: Scans 10,010 rows
SELECT * FROM transaction_log ORDER BY created_at LIMIT 10000, 10;

-- Efficient: Uses covering index to find IDs, then joins
SELECT t.* 
FROM transaction_log t
INNER JOIN (
    SELECT id 
    FROM transaction_log 
    ORDER BY created_at 
    LIMIT 10000, 10
) AS tmp USING (id);

Batching Large Operations

Large delete or update operations can lock tables for extended periods. Breaking them into smaller batches reduces contention.

-- Instead of deleting all at once
-- DELETE FROM audit_logs WHERE created_date < NOW() - INTERVAL 3 MONTH;

-- Use a batched approach
SET @rows_affected = 1;
WHILE @rows_affected > 0 DO
  DELETE FROM audit_logs 
  WHERE created_date < NOW() - INTERVAL 3 MONTH 
  LIMIT 5000;
  SET @rows_affected = ROW_COUNT();
END WHILE;

Optimizing MIN() Queries

When fetching the minimum value from an indexed column, using LIMIT 1 with an ORDER BY clause can sometimes be as efficient as the MIN() aggregate function.

-- Standard approach
SELECT MIN(id) FROM items;

-- Alternative using index ordering
SELECT id FROM items ORDER BY id ASC LIMIT 1;

Conditional Counting

To count occurrences of multiple specific values in a single pass, use conditional aggregation rather than multiple queries.

SELECT 
    SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) AS admin_count,
    SUM(CASE WHEN role = 'editor' THEN 1 ELSE 0 END) AS editor_count
FROM users;

Join and Group By Optimization

Ensure columns used in JOIN conditions are indexed. When using GROUP BY, referencing columns from a single table allows the optimizer to utilize indexes more effectively.

Subquery Grouping

Moving GROUP BY operations into a subquery before joining can significantly reduce the cost by processing fewer rows.

-- Higher cost join
SELECT u.id, u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Lower cost using subquery
SELECT u.id, u.name, summary.total
FROM users u
INNER JOIN (
    SELECT user_id, SUM(amount) as total
    FROM orders
    GROUP BY user_id
) AS summary ON u.id = summary.user_id;

Implicit Group By Sorting

By default, MySQL sorts results when using GROUP BY. If sorting is not required, explicitly disable it to save resources.

-- Implicit sort performed
SELECT department, COUNT(*) FROM staff GROUP BY department;

-- Sort explicitly disabled
SELECT department, COUNT(*) FROM staff GROUP BY department ORDER BY NULL;

Covering Indexes

A covering index includes all columns required by a query (SELECT, JOIN, WHERE). This allows the storage engine to satisfy the query solely from the index tree without accessing the data rows (table lookup).

Covering Indexes for Pagination

Applying the covering index technique to pagination drastically improves performance by preventing the engine from reading full rows for offset entries.

-- This query benefits if there is an index on (salary, id)
SELECT id, employee_name 
FROM compensation 
INNER JOIN (
    SELECT id 
    FROM compensation 
    ORDER BY salary 
    LIMIT 10000, 10
) AS tmp USING (id);

Query Profiling

To analyze the execution time of queries, enable profiling. Note that this feature is deprecated in newer versions in favor of the Performance Schema, but remains useful for older environments.

SET profiling = 1;

SELECT * FROM large_table WHERE random_col > 50;

SHOW PROFILES;

SET profiling = 0;

Query Cache Limitations

Queries containing non-deterministic functions like NOW(), CURRENT_DATE(), or RAND() cannot be cached, as their results change with every execution.

Full-Text Search Indexes

Full-text indexes utilize an inverted index structure. A dictionary of unique words is maintained, mapping each word to the list of document IDs containing it. This allows for rapid retrieval of records containing specific terms.

System Scalability

Scalability refers to the system's ability to handle increasing loads while maintaining performance. Architectural strategies for scalability include:

• Master-Master Replication: Active-Active topology for read/write distribution.
• Master-Slave Replication: Read scaling by offloading queries to replicas.
• Sharding: Horizontal partitioning of data across multiple servers.
• Shared Storage Clusters: Using SAN or NAS for distributed data access.