Top MySQL Interview Questions & Answers Every DBA Should Know

1. Difference between primary key and candidate key

A candidate key can be any column or combination of columns that uniquely identifies rows; a table may have multiple candidate keys, each of which could serve as the primary key. The primary key is the chosen unique identifier, and only one candidate key can become the primary key.

2. Difference between NOW() and CURRENT_DATE()

NOW() returns the current date and time (year‑month‑day hour:minute:second). CURRENT_DATE() returns only the current date (year‑month‑day).

3. How many triggers are allowed on a MySQL table?

Before INSERT

After INSERT

Before UPDATE

After UPDATE

Before DELETE

After DELETE

4. Maximum number of columns that can be used in an index

16

5. How to copy data from one table to another

INSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1

6. How to copy a table without copying its data

CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;

7. What is MySQL?

MySQL is a free, open‑source relational database management system (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing, and managing data in databases, known for its speed, reliability, ease of use, and flexibility. MySQL powers most open‑source PHP applications such as WordPress, Joomla, Magento, and Drupal.

8. Key characteristics of MySQL

Relational database management system (RDBMS)

Easy to use – basic SQL knowledge is sufficient

Secure – includes a robust data‑security layer with encrypted passwords

Scalable – can handle tens of millions of rows, with file size limits extendable to several terabytes

Cross‑platform – runs on many operating systems

Supports transaction rollback, commit, and crash recovery

High performance due to its storage‑engine architecture

Highly flexible – supports many embedded applications

Boosts developer productivity with triggers, stored procedures, and views

9. Meaning of transaction and ACID properties

A transaction is a logical unit of work that must be fully completed or not executed at all. ACID stands for Atomicity, Consistency, Isolation, and Durability – the essential properties of reliable transactions.

10. What is a heap table?

A heap table has no clustered index. One or more non‑clustered indexes can be created on it. Data is stored without a defined order; rows are initially inserted in the order they arrive, but the engine may rearrange them for storage efficiency, making the physical order unpredictable. To guarantee row order, use ORDER BY, or create a clustered index to convert the table from a heap.

11. Heap table vs. clustered table

Heap table

Data not stored in any specific order

Fast retrieval only with non‑clustered indexes

Data pages are not linked, requiring IAM page lookups for sequential access

No overhead for maintaining a clustered index

No extra space for a clustered index tree

Index_id value in sys.indexes is 0

Clustered table

Data stored in order of the clustered index key

Fast retrieval when queries use indexed columns

Linked data pages enable faster sequential access

Additional time required to maintain the clustered index on INSERT, UPDATE, DELETE

Extra space needed for the clustered index tree

Index_id value in sys.indexes is 1

12. Difference between FLOAT and DOUBLE

FLOAT stores numbers with 4 bytes (approximately 8‑digit precision)

DOUBLE stores numbers with 8 bytes (approximately 18‑digit precision)

13. Objects that can be created with the CREATE statement

DATABASE

EVENT

FUNCTION

INDEX

PROCEDURE

TABLE

TRIGGER

USER

VIEW

14. Difference between primary key and unique key

Both enforce uniqueness, but a primary key creates a clustered index and does not allow NULL values, whereas a unique key creates a non‑clustered index and permits NULLs.