Storage Engines

The primary difference between MyISAM and InnoDB lies in transaction support and locking granularity. InnoDB supports transactions and row-level locking, whereas MyISAM offers table-level locking and lacks transaction capabilities.

Join Operations

SQL Execution Flow

The database engine processes queries starting with the FROM clause, followed by joins, WHERE, GROUP BY, HAVING, SELECT, and finally ORDER BY.

Join Types

• Inner Join: Returns records with matching values in both tables.
• Left Join: Returns all records from the left table and matched records from the right.
• Right Join: Returns all records from the right table and matched records from the left.
• Full Outer Join: MySQL does not natively support FULL OUTER JOIN. Use a UNION of a left join and a right join to achieve this result.

SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.a_id
UNION
SELECT * FROM table_a RIGHT JOIN table_b ON table_a.id = table_b.a_id;

Endexing

Definition

An index is a data structure (commonly a B+ Tree) that improves data retrieval speed. It acts like a sorted lookup structure pointing to the actual data rows. While indexes accelerate reads, they consume disk space and require maintenance during write operations (INSERT, UPDATE, DELETE).

Index Types

• Single-Column Index: An index on a single column.
• Unique Index: Ensures column values are unique, allowing nulls.
• Composite Index: An index spanning multiple columns.

B+ Tree Structure

Most standard indexes in MySQL use a B+ Tree structure, which keeps data sorted and allows for efficient range queries and sequential access.

When to Create Indexes

Suitable for:

• Columns frequently used in WHERE clauses.
• Columns used in JOIN conditions.
• Columns used in ORDER BY or GROUP BY.

Unsuitable for:

• Columns with low cardinality (e.g., gender fields with only 'M'/'F').
• Columns that are frequently updated.
• Very small tables where a full scan is faster.

Performance Analysis with EXPLAIN

Use EXPLAIN followed by a SQL statement to view the execution plan.

Key Fields in Execution Plan

• id: The sequence number of the SELECT. Higher numbers run first, or same numbers run sequentially.
• select_type: The type of query (e.g., SIMPLE, PRIMARY, SUBQUERY, DERIVED).
• type: The access type. Optimal order (best to worst): system > const > eq_ref > ref > range > index > ALL. Aim for at least range.
• possible_keys: Indexes MySQL could use.
• key: The index MySQL actually used.
• rows: Estimated number of rows scanned.
• Extra: Additional info like Using index (covering index), Using filesort (manual sorting), or Using temporary (using temp table).

Index Optimization

Multi-Table Joins

When joining tables, ensure the "driven" table (the one being looked up) has an index on the join column. Generally, drive the query with the smaller result set.

-- Optimizing a join between categories and products
SELECT * FROM categories cat
JOIN products prod ON cat.id = prod.category_id
WHERE cat.status = 'active';
-- Ensure an index exists on products.category_id

Avoiding Index Failure

1. Leftmost Prefix Rule: For composite indexes, queries must start from the first column defined in the index. Skipping columns breaks the index usage.
2. Range Queries: Columns used in range conditions (>, <, BETWEEN) cause columns to the right in the composite index to be ignored.
3. Function Usage: Using functions on indexed columns (e.g., WHERE YEAR(date_col) = 2023) invalidates the index.
4. Wildcards: Leading wildcards in LIKE (e.g., %term) prevent index usage. Trailing wildcards (term%) usually allow index usage.
5. Type Conversion: Comparing a string column with a numeric value without quotes forces MySQL to convert types, bypassing the index.

Query Profiling Workflow

1. Capture Slow Queries: Enable the slow query log to capture queries exceeding a specific threshold (e.g., 1 second).

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   

2. Analyze with EXPLAIN: Review the execution plan of the captured slow queries.
3. Profile Execution: Use SHOW PROFILES to analyze resource consumption (CPU, IO) for specific queries.

   SET profiling = 1;
   -- Run your query
   SHOW PROFILES;
   SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
   

Sorting and Grouping

• ORDER BY: Use indexes for sorting whenever possible to avoid filesort. The ORDER BY clause must match the order of columns in the index.
• GROUP BY: Grouping implies sorting. Follow the leftmost prefix rule of indexes. If indexes cannot be used, consider increasing sort_buffer_size.

Locking Mechanisms

Table Locks (MyISAM)

• Read Lock: Shared. Multiple sessions can read, but no session can write.
• Write Lock: Exclusive. Only the session holding the lock can read or write; others are blocked.

Row Locks (InnoDB)

• Characteristics: Higher overhead, supports transactions, and allows higher concurrency.
• Gap Locks: InnoDB locks the index record and the gap before it to prevent phantom reads.
• Failure Case: If a query does not hit an index, a row lock escalates to a full table lock.

Analysis

Check row lock contention:

SHOW STATUS LIKE 'innodb_row_lock%';

Master-Slave Replication

Principle

The Slave server connects to the Master and reads the binary log (binlog). It replays these events to replicate data changes.

Configuration Steps

1. Master Config (my.cnf):

   [mysqld]
   log-bin=mysql-bin
   server-id=1
   

2. Slave Config (my.cnf):

   [mysqld]
   server-id=2
   

3. Create User on Master:

   CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';
   FLUSH PRIVILEGES;
   

4. Initiate Replication on Slave:

   CHANGE MASTER TO
   MASTER_HOST='master_host',
   MASTER_USER='repl_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=107;
   START SLAVE;
   

The primary limitation in replication is latency (delay) between the Master and Slave.