Key MySQL Interview Questions: Engine Differences, Type Conversion, Random Row Retrieval, and Index Types

This article is part of a series of MySQL interview questions, encouraging readers to follow the public account for more content.

1. Differences between MyISAM and InnoDB in MySQL

MyISAM:

Does not support transactions, but each query is atomic.

Uses table‑level locking; the whole table is locked during an operation.

Stores the total row count for the table.

A MyISAM table consists of three files: index file, table definition file, and data file.

Uses a non‑clustered index; the index file stores pointers to the data file. Secondary indexes are similar to primary indexes but do not enforce uniqueness.

InnoDB:

Supports ACID transactions and four isolation levels.

Provides row‑level locking and foreign‑key constraints, enabling concurrent writes.

Does not store a total row count.

Data can be stored in a shared tablespace (a single file that may span multiple files) or in separate tablespaces limited by the operating system file size (typically 2 GB).

Primary key index is clustered (the index stores the actual row data). Secondary indexes store the primary key value; therefore a lookup via a secondary index first retrieves the primary key and then accesses the row. Using an auto‑increment primary key is recommended to avoid large B‑tree adjustments during inserts.

Applicable scenarios:

MyISAM is faster for read‑heavy, write‑light workloads where atomicity requirements are low; it also recovers quickly by overwriting with a backup. InnoDB is preferred for write‑intensive or highly concurrent environments because of its transaction support and row‑level locking.

Extended question: Which engine executes SELECT COUNT(*) faster and why?

2. Implicit type conversion between INT and CHAR in MySQL

Key points to remember:

When the column is INT and the condition is a CHAR, MySQL converts the condition to an integer. If the string starts with digits, the leading numeric part is used; otherwise the value becomes 0.

When the column is CHAR/VARCHAR and the condition is an INT, MySQL converts the column to an integer for comparison, which may cause a full table scan.

Answer explanation using a sample table product (id INT, name VARCHAR):

+----+----------+
| id | name     |
+----+----------+
|  1 | apple    |
|  2 | banana   |
|  3 | 99cat    |
+----+----------+

Case 1 – INT column with CHAR condition:

// Query condition is converted to number 1 before comparison
mysql> select * from product where id = '1abc23';
+----+---------+
| id | name    |
+----+---------+
|  1 | apple   |
+----+---------+

Case 2 – CHAR column with INT condition:

// All column values are converted to numbers; id 1 and 2 become 0, id 3 becomes 99
mysql> select * from product where name = 0;
+----+----------+
| id | name     |
+----+----------+
|  1 | apple    |
|  2 | banana   |
+----+----------+

3. Efficiently retrieving N random rows in MySQL

Assume a table mm_account with an id column.

When IDs are continuous (no WHERE clause):

SELECT *
FROM `mm_account` AS t1
JOIN (
  SELECT ROUND(RAND() * (SELECT MAX(id) FROM `mm_account`)) AS id
) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC
LIMIT 4;

When IDs are not continuous:

SELECT *
FROM `mm_account`
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `mm_account`)))
  AND city = "city_91"
  AND showSex = 1
ORDER BY id
LIMIT 4;

Another fast method for a generic id column to fetch 5 random rows:

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

When a WHERE clause is required (example with id < 1000 ):

SELECT *
FROM `mm_account` AS t1
JOIN (
  SELECT ROUND(RAND() * (
    SELECT MAX(id) FROM `mm_account` WHERE id < 1000
    ) - (
    SELECT MIN(id) FROM `mm_account` WHERE id < 1000
    )) + (SELECT MIN(id) FROM `mm_account` WHERE id < 1000) AS id
) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id
LIMIT 5;

4. MySQL index types and their typical scenarios

Ordinary index: No restrictions; can be created on any column type.

Unique index: Enforces uniqueness (NULLs allowed); primary key is a special unique index.

Full‑text index: Available only for MyISAM tables; suitable for large text search but costly to build.

Spatial index: Works on spatial data types, also limited to MyISAM and cannot contain NULL values; improves queries on geometric data.

Single‑column index: Indexes a single column.

Composite (multi‑column) index: Indexes multiple columns together; queries must use at least one of the indexed columns to benefit.

Extended question: How do indexes improve query efficiency?