Top MySQL Interview Questions: Data Types, Indexes, Joins, and Performance Tuning

MySQL Interview Topics Overview

This summary consolidates core MySQL concepts frequently asked in interviews, covering data types, storage engines, locking, transactions, triggers, indexes, join types, and SQL optimization techniques.

Basic Questions

CHAR vs VARCHAR : CHAR is fixed‑length and pads with spaces; VARCHAR stores variable‑length strings with a 1‑2 byte length prefix. VARCHAR usually offers better space efficiency for non‑fixed data.

TRUNCATE vs DELETE : DELETE removes rows one by one and can be rolled back; TRUNCATE drops all rows instantly without row‑level logging and cannot be rolled back.

Triggers : Six trigger events exist – BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE.

FLOAT vs DOUBLE : FLOAT stores up to 8 decimal digits in 4 bytes; DOUBLE stores up to 18 decimal digits in 8 bytes.

Current date : SELECT CURRENT_DATE(); Nth highest salary :

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT n-1,1;

Data Types

MySQL provides integer, real, string, enum, and date/time families.

Integer types (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) can be unsigned. Length specifications (e.g., INT(11)) affect only display width.

Floating‑point types ( FLOAT, DOUBLE) are approximate; DECIMAL offers exact precision. VARCHAR stores variable‑length strings with 1‑2 extra bytes for length; CHAR is fixed‑length and pads with spaces.

Prefer CHAR for very short, frequently updated columns to avoid fragmentation; avoid TEXT / BLOB when possible because they force temporary tables.

Storage Engines

InnoDB : Transactional, row‑level locking, crash‑safe, supports foreign keys, uses clustered primary key indexes.

MyISAM : Non‑transactional, table‑level locking, supports full‑text indexes, stores data in .MYD and .MYI files.

Other engines: Archive, Blackhole, CSV, Memory.

In most production scenarios, InnoDB is the recommended engine.

Lock Mechanisms

MySQL distinguishes shared (read) locks, which do not block other reads, and exclusive (write) locks, which block both reads and writes. Table‑level locks (used by MyISAM) have minimal overhead, while row‑level locks (used by InnoDB) provide higher concurrency.

Transaction Processing

Only transactional engines (e.g., InnoDB) support true ACID transactions. Mixing transactional and non‑transactional tables in a single transaction is unreliable; operations on non‑transactional tables are not rolled back.

Triggers

Triggers execute automatically on defined table events, useful for data integrity, cascading changes, or auto‑numbering. Overuse can increase maintenance complexity.

Indexes

Indexes accelerate data retrieval by allowing the server to locate rows without scanning the whole table. They increase write cost and disk usage, so design must balance read‑performance gains against write overhead.

Index Types

Ordinary (non‑unique) index

Unique index – enforces uniqueness

Primary key – unique, NOT NULL, clustered in InnoDB

Composite (multi‑column) index

Foreign‑key index – created automatically for InnoDB foreign keys

Full‑text index – supported by MyISAM for English text

Creating and Dropping Indexes

CREATE TABLE t1 (
  id INT PRIMARY KEY,
  username VARCHAR(20),
  password VARCHAR(20)
);
CREATE INDEX idx_username ON t1(username);
CREATE INDEX idx_user_pass ON t1(username, password);  -- composite index
DROP INDEX idx_user_pass ON t1;

Design Principles

Index columns used in WHERE, ON, or JOIN conditions.

Prefer high‑cardinality columns.

For long string columns, index a prefix length to save space.

Avoid excessive indexes; they waste space and slow writes.

Keep primary keys short (e.g., INT AUTO_INCREMENT).

Common Pitfalls

Applying functions or arithmetic on indexed columns (e.g., WHERE YEAR(date) < 2007) disables index usage.

Leading wildcards in LIKE ( LIKE "%text%") prevent index use.

NULL columns can be indexed but complicate optimizer decisions.

OR conditions where only one side is indexed cause a full table scan.

Join Types

CROSS JOIN – Cartesian product, rarely useful.

INNER JOIN – returns rows with matching keys.

LEFT / RIGHT OUTER JOIN – returns all rows from one side, filling missing matches with NULL.

UNION / UNION ALL – combine result sets; UNION removes duplicates, UNION ALL keeps them.

FULL JOIN – not directly supported; can be simulated with LEFT JOIN … UNION … RIGHT JOIN.

Join Examples

SELECT * FROM A INNER JOIN B ON A.id = B.id;
SELECT * FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

SQL Optimization Techniques

Typical workflow for diagnosing slow queries:

Enable the slow‑query log and analyze with pt‑query‑digest.

Set profiling: SET profiling = 1; then view with SHOW PROFILES; and SHOW PROFILE FOR QUERY n;.

Inspect SHOW STATUS / SHOW GLOBAL STATUS for server counters.

Check SHOW PROCESSLIST for blocked or long‑running threads.

Run EXPLAIN on statements to view the execution plan.

Data‑Access Optimizations

Avoid SELECT *; list only needed columns.

Ensure appropriate indexes exist for columns in WHERE, ON, or JOIN clauses.

Prefer LIMIT with a known maximum ID over large offsets.

Replace subqueries with joins when possible.

Use covering indexes to eliminate back‑table lookups.

Query Rewrite Tips

Replace NOT IN or OR conditions with UNION or separate indexed queries.

Prefer BETWEEN over IN for continuous numeric ranges.

Rewrite expressions on indexed columns to use the column directly (e.g., WHERE num = 10 instead of WHERE num/2 = 5).

Use a leading pattern in LIKE ( col LIKE 'abc%') to enable index usage.

Pagination

For large offsets, store the last retrieved primary key and query with WHERE id > last_id LIMIT 20 instead of using a high OFFSET.

UNION vs UNION ALL

UNION ALL

is faster because it skips the duplicate‑removal step.

Efficient Random Sampling

SELECT * FROM users
WHERE id >= (
  SELECT MAX(id) - MIN(id) FROM users
) * RAND() + MIN(id)
LIMIT 10;

This approach is far more efficient than ORDER BY RAND() LIMIT 10.

Practical SQL Patterns

Copy a table: INSERT INTO b(a,b,c) SELECT d,e,f FROM a; Update with join:

UPDATE A INNER JOIN B ON A.id = B.id
SET A.c1 = B.c1, A.c2 = B.c2
WHERE B.age > 50;

Find rows that never appear in a related table using NOT IN subqueries.

Design multi‑table queries for complex domains (e.g., football match results) by listing required columns, joining tables on foreign keys, and applying date ranges.

Index Creation Principles (Recap)

Index columns appearing in WHERE, ON, or JOIN clauses.

Higher cardinality yields more selective indexes.

For string columns, index a prefix length to reduce index size.

Use composite indexes when queries filter on multiple columns in the same order.

Avoid over‑indexing; each index adds write overhead and consumes disk space.

Keep primary key columns as short as possible.

Index Usage Caveats

Composite indexes follow the left‑most prefix rule; queries must use the leading columns in order.

Leading wildcards in LIKE disable index usage.

Functions on indexed columns (e.g., YEAR(date)) prevent index use.

OR conditions where only one operand is indexed cause the optimizer to forgo the index.

NULL values are indexed but can make optimizer statistics less accurate.

InnoDB Clustered Index Details

InnoDB stores the primary key together with the row data (clustered index). If no primary key is defined, InnoDB picks a unique non‑NULL index or creates a hidden 6‑byte row ID. Secondary indexes store the primary key value, enabling covering indexes but requiring an extra lookup to fetch the full row.

Insert speed is optimal when rows are inserted in primary‑key order; otherwise page splits degrade performance.

Updating the primary key is expensive because it moves the row.

Secondary index lookups involve two steps: locate the primary key, then fetch the row.

Analyzing Slow Queries

Enable the slow‑query log and feed the log to pt‑query‑digest for aggregation.

Use SET profiling = 1; and SHOW PROFILE FOR QUERY n; to see time spent in each stage.

Run EXPLAIN to verify that indexes are used and that the optimizer does not choose a full table scan.

Check SHOW PROCESSLIST for blocked threads.

Typical Optimization Checklist

Eliminate SELECT * and unnecessary columns.

Ensure indexes exist for filter and join columns.

Rewrite expressions to avoid functions on indexed columns.

Prefer BETWEEN or range scans over IN for contiguous values.

Use UNION ALL when duplicate removal is not required.

Implement keyset pagination ( WHERE id > last_id LIMIT n) for large result sets.

Consider covering indexes to avoid back‑table lookups.

Example: Updating One Table from Another

UPDATE A
INNER JOIN B ON A.id = B.id
SET A.c1 = B.c1, A.c2 = B.c2
WHERE B.age > 50;

Example: Copying Data Between Tables

INSERT INTO target(col1, col2, col3)
SELECT src1, src2, src3 FROM source;

Example: Random Row Sampling (Efficient)

SELECT * FROM users
WHERE id >= (
  SELECT MAX(id) - MIN(id) FROM users
) * RAND() + MIN(id)
LIMIT 10;

Key Takeaways

Understand the characteristics of InnoDB vs MyISAM and choose InnoDB for transactional workloads.

Design indexes deliberately: index the columns that filter or join, keep them short, and respect the left‑most prefix rule for composite indexes.

Avoid patterns that disable index usage (functions, leading wildcards, OR without full coverage).

Use profiling tools ( SHOW PROFILE, pt‑query‑digest) and EXPLAIN to locate bottlenecks.

Rewrite queries to reduce data scanned, leverage covering indexes, and apply efficient pagination techniques.