Top MySQL Interview Questions & Answers: Indexes, Engines, and Optimization

MySQL Interview Questions Overview

This collection includes essential MySQL interview topics beyond basic questions, such as high‑performance indexing, SQL statements, query optimization, high‑availability, and security.

Question 1: Difference between CHAR and VARCHAR

VARCHAR is variable‑length, while CHAR has a fixed length; fixed‑length fields can offer better performance when the size is constant.

Question 2: Difference between TRUNCATE and DELETE

DELETE removes specific rows from a table, whereas TRUNCATE permanently removes all rows.

Question 3: What are triggers and their types in MySQL?

A trigger is code that automatically executes when a specific event occurs. MySQL supports six triggers:

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

Question 4: Difference between FLOAT and DOUBLE

FLOAT stores up to 8 decimal digits and occupies 4 bytes.

DOUBLE stores up to 18 decimal digits and occupies 8 bytes.

Question 5: Get current date in MySQL

SELECT CURRENT_DATE();

Question 6: Query the nth highest salary

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

Question 7: Meaning of data types int(0), char(16), varchar(16), datetime, text

int(0) denotes an INT type; char(16) is a fixed‑length string of 16 characters; varchar(16) is a variable‑length string up to 16 characters; datetime stores date and time; text stores large strings (up to 65 535 bytes).

MySQL Fundamentals

Data Types Overview

Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT (1‑8 bytes, optional UNSIGNED).

Real types: FLOAT, DOUBLE, DECIMAL (DECIMAL provides exact precision).

String types: VARCHAR, CHAR, TEXT, BLOB (VARCHAR stores length + data, CHAR pads with spaces).

Enum type: ENUM stores predefined values compactly as integers.

Date/Time types: Prefer TIMESTAMP for space efficiency; DATETIME for readability.

Usage Strategies

Use CHAR for frequently updated short columns to avoid fragmentation.

Avoid TEXT/BLOB when possible because they trigger temporary tables.

Basic Operations

Connecting and disconnecting: mysql -u user -p -h host -P port Inside the MySQL client:

G – vertical output

c – cancel current command

q – quit

s – show server status

h – help

d – change delimiter

Storage Engines

InnoDB – default transactional engine, row‑level locking, crash recovery, foreign keys, clustered index.

MyISAM – supports full‑text index, no transactions or row‑level lock, stores data in .MYD and .MYI files.

Other engines: Archive, Blackhole, CSV, Memory.

Recommendation: Use InnoDB for most scenarios.

Lock Mechanisms

Shared lock (read lock) – non‑blocking, multiple readers.

Exclusive lock (write lock) – blocks other reads and writes.

Granularity: table lock (MyISAM) vs row lock (InnoDB).

Transaction Handling

Supported by InnoDB; non‑transactional engines ignore transaction statements.

Stored Procedures

Encapsulate one or more SQL statements, can include business logic, create tables, update, delete, etc.

Triggers

Special stored procedures tied to table events, used for data integrity, cascading changes, real‑time monitoring.

Indexes

Basics

Indexes act like a book's table of contents, allowing fast row location.

Creation Syntax

CREATE INDEX idx_name ON table_name(column_name);

CREATE INDEX idx_name ON table_name(col1, col2);

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

CREATE PRIMARY KEY (column_name);

DROP INDEX idx_name ON table_name;

Impact on Performance

Reduces scanned rows.

Avoids sorting and temporary tables.

Turns random I/O into sequential I/O.

Speeds up queries.

May slow writes and increase disk usage.

Usage Scenarios

Small tables – full scan may be faster.

Medium to large tables – indexes are effective.

Very large tables – consider partitioning.

Index Types

Normal (non‑unique) index.

Unique index.

Primary key (unique, not null).

Composite (multi‑column) index.

Foreign key index (InnoDB only).

Full‑text index (MyISAM only).

Creation Principles

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

Higher cardinality columns are better candidates.

Use prefix lengths for long string columns.

Avoid excessive indexes to reduce storage and write overhead.

Prefer short data types for primary keys.

Important Notes

Composite indexes follow the left‑most prefix rule.

LIKE patterns with leading % cannot use indexes.

NULL columns can use indexes but complicate optimization.

Functions or arithmetic on indexed columns prevent index usage.

OR conditions may disable index usage; rewrite with UNION ALL when possible.

Index Principles Demonstrated

KEY(a,b,c)
WHERE a=1 AND b=2 AND c=3   # uses index
WHERE a=1 AND b=2           # uses index (prefix)
WHERE a=1                  # uses index (prefix)
WHERE b=2 AND c=3          # does NOT use index

Query Types

Join Types

CROSS JOIN – Cartesian product.

INNER JOIN – rows matching ON condition.

LEFT/RIGHT OUTER JOIN – keep all rows from left/right table.

UNION / UNION ALL – combine result sets (UNION removes duplicates).

FULL JOIN – not directly supported; emulate with LEFT JOIN + UNION + RIGHT JOIN.

Nested Queries

Use subqueries, e.g.,

SELECT * FROM A WHERE id IN (SELECT id FROM B);

Optimization Techniques

Record and analyze slow query logs (use pt‑query‑digest).

Enable profiling: SET profiling = 1; then SHOW PROFILES; Use SHOW STATUS and SHOW PROCESSLIST for server‑wide metrics.

Analyze statements with EXPLAIN.

Avoid SELECT *; specify needed columns.

Prefer LIMIT for pagination; use “max ID” technique for large offsets.

Replace IN/NOT IN with EXISTS or JOIN when possible.

Avoid functions, arithmetic, or expressions on indexed columns.

Use UNION ALL instead of UNION when duplicate removal is unnecessary.

Sample Optimizations

SELECT id FROM t WHERE num IS NULL;   -- better to set default 0 and query =0

SELECT id FROM t WHERE num=10 OR num=20;   -- rewrite as UNION ALL of two simple queries

SELECT id FROM t WHERE num IN (1,2,3);   -- use BETWEEN 1 AND 3 when range is continuous

SELECT id FROM t WHERE name LIKE '%abc%';   -- consider full‑text index or external search engine

Practical Examples

Update with Join

UPDATE A,B SET A.c1=B.c1, A.c2=B.c2 WHERE A.id=B.id;

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

Random Row Selection

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

Copy Table Data

INSERT INTO b(a,b,c) SELECT d,e,f FROM a;

Find Students Not Taking Teacher Ye Ping's Courses

SELECT Student.S FROM Student WHERE Student.S NOT IN (SELECT DISTINCT SC.S FROM SC, Course, Teacher WHERE SC.C=Course.C AND Teacher.T=Course.T AND Teacher.Tname='叶平');

Match Result Query Example

SELECT t1.teamName, m.matchResult, t2.teamName, m.matchTime FROM match AS m LEFT JOIN team AS t1 ON m.hostTeamID=t1.teamID LEFT JOIN team AS t2 ON m.guestTeamID=t2.teamID WHERE m.matchTime BETWEEN '2006-6-1' AND '2006-7-1';

Images illustrating index concepts are retained:

Source: Collected from the web.