Mastering MySQL: Keys, Transactions, Indexes, Joins, and Optimization Essentials

1. Primary Key, Super Key, Candidate Key, Foreign Key

Super key : any attribute set that can uniquely identify a tuple in a relation.

Candidate key : a minimal super key without redundant attributes.

Primary key : a candidate key chosen by the user to identify tuples.

Foreign key : an attribute set in relation R1 that references the primary key of another relation R2.

Example

Assume two tables:

Student(student_id, name, gender, id_number, teacher_id)

Teacher(teacher_id, name, salary)

Super keys : any combination containing student_id or id_number, e.g., (student_id), (student_id, name), (id_number, gender).

Candidate keys : student_id and id_number.

Primary key : typically student_id for Student and teacher_id for Teacher.

Foreign key : teacher_id in Student, describing the relationship between the two tables.

2. Database Transactions

Four basic ACID properties: Atomicity, Consistency, Isolation, Durability.

Atomicity : all operations in a transaction succeed or none do; failures cause a rollback.

Consistency : integrity constraints are preserved before and after the transaction.

Isolation : transactions appear to run alone; concurrent transactions are serialized to avoid interference.

Durability : once a transaction commits, its changes persist even after crashes.

3. Views

A view is a virtual table that does not store data but provides a stored query for dynamic retrieval. Views simplify complex SQL, hide details, and protect data. They cannot be indexed, have triggers, or default values, and an ORDER BY inside a view is overridden by an outer ORDER BY.

Creating a view: CREATE VIEW view_name AS SELECT ...; Some views (without joins, subqueries, aggregates, etc.) can be updatable; updates affect the underlying base tables.

4. Differences Between DROP, DELETE, and TRUNCATE

DROP removes the entire table and its definition.

TRUNCATE deletes all rows but retains the table structure; it resets auto‑increment counters and does not log individual row deletions.

DELETE removes rows based on a WHERE clause; each row deletion is logged and can be rolled back.

Key points:

DELETE logs each row; TRUNCATE logs only the statement.

TRUNCATE frees space immediately; DELETE does not.

Performance order: DROP > TRUNCATE > DELETE.

TRUNCATE works only on tables, not on views.

DROP removes constraints, triggers, and indexes; TRUNCATE and DELETE keep them.

5. Indexes: How They Work and Types

Indexes are sorted data structures (usually B‑Tree or B+Tree) that speed up queries and updates. They consume storage space and add overhead on inserts/updates.

Advantages :

Enforce uniqueness.

Accelerate data retrieval.

Speed up joins and referential integrity checks.

Reduce sorting and grouping time.

Enable optimizer to choose efficient execution plans.

Disadvantages :

Maintenance overhead grows with data size.

Consume additional physical storage.

Slow down inserts, updates, and deletes.

Recommended columns for indexing :

Frequently searched columns.

Primary key columns.

Foreign key columns used in joins.

Columns used in range queries or ORDER BY.

Columns appearing often in WHERE clauses.

Not recommended :

Columns rarely used in queries.

Low‑cardinality columns (e.g., gender).

Large TEXT, IMAGE, or BIT columns.

When write performance is more critical than read performance.

Index categories :

Unique index: enforces distinct values.

Primary key index: a special unique index automatically created for the primary key.

Clustered index: rows are stored in the order of the index key; only one per table.

6. Types of Joins

Outer joins :

Left join:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

Right join:

SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

Full outer join:

SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;

Inner join : SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; Equivalent forms using commas and WHERE clauses are also shown.

Cross join (Cartesian product) :

Without a WHERE clause: SELECT * FROM table1 CROSS JOIN table2; Equivalent to

SELECT * FROM table1, table2;

7. Database Normalization

1NF : each column holds atomic, indivisible values; no repeating groups.

2NF : meets 1NF and every non‑key attribute is fully dependent on the whole primary key (no partial dependencies).

3NF : meets 2NF and non‑key attributes are not transitively dependent on the primary key.

8. Optimization Strategies

SQL statement optimization :

Avoid != or <> in WHERE clauses to keep index usage.

Avoid IS NULL checks; prefer default values.

Prefer EXISTS over IN where appropriate.

Use WHERE instead of HAVING when possible.

Index optimization : apply the index guidelines above.

Schema optimization :

Normalize to eliminate redundancy.

Denormalize selectively to reduce joins.

Vertical or horizontal table partitioning.

9. MySQL Replication Architecture

Three threads:

Master: binlog thread records all data‑changing statements to the binary log.

Slave I/O thread: pulls the binlog from the master into the relay log.

Slave SQL thread: executes statements from the relay log.

10. MyISAM vs InnoDB (at least five differences)

InnoDB supports transactions; MyISAM does not.

InnoDB uses row‑level locking; MyISAM uses table‑level locking.

InnoDB provides MVCC; MyISAM does not.

InnoDB supports foreign keys; MyISAM does not.

MyISAM supports full‑text indexes; InnoDB historically did not.

11. Four InnoDB Features

Insert buffer, doublewrite buffer, adaptive hash index, and read‑ahead (pre‑fetch).

12. Count(*) Performance

MyISAM is faster because it maintains a row count internally.

13. VARCHAR vs CHAR and Length Meaning

CHAR is fixed‑length; VARCHAR is variable‑length.

VARCHAR(50) can store up to 50 characters; the length does not affect storage for short values but influences memory usage during sorting.

INT(20) specifies display width, not storage size.

14. InnoDB Transaction and Log Implementation

Log types: error log, general query log, slow query log, binary log, relay log, transaction log.

Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Transactions use redo logs and the InnoDB log buffer; on commit the log buffer is flushed to disk (pre‑write logging).

15. Binlog Formats

Statement format records the SQL statement; low log volume but requires additional context for correct replay.

Row format records changed rows; high fidelity but larger log size.

Mixed format chooses between statement and row per statement.

16. Handling MySQL CPU Spikes

Show processes: SHOW PROCESSLIST; Identify long‑running idle queries and terminate them.

Check slow query, error, and timeout logs for problematic statements.

17. Efficient Pagination on Large Tables

If TID is auto‑increment and continuous:

SELECT * FROM a,b WHERE a.tid = b.id AND a.tid > 500000 LIMIT 200;

If TID is not continuous, use a covering index:

SELECT * FROM b, (SELECT tid FROM a LIMIT 50000,200) a WHERE b.id = a.tid;

18. InnoDB Row Locks

Row locks are based on indexed columns; without an index, InnoDB falls back to table locks.

19. XtraBackup Principle

InnoDB maintains redo logs; on startup, it applies committed redo logs to data files and rolls back uncommitted changes.

20. Stored Procedures vs Triggers

Triggers fire automatically on data‑modifying events and cannot be invoked manually; stored procedures are called explicitly by name.