Essential MySQL Interview Questions and Answers: Locks, Indexes, Transactions, and More

1. What lock types exist in MySQL?

Table‑level lock : low overhead, fast to acquire, no deadlocks, but coarse granularity leads to high lock‑conflict probability and low concurrency. Row‑level lock : higher overhead and slower acquisition, can cause deadlocks, fine granularity gives low conflict probability and high concurrency. Page‑level lock : overhead and acquisition time between table and row locks, can cause deadlocks, moderate granularity and concurrency.

2. What table types are available in MySQL?

MySQL supports five table engines: MyISAM, Heap, Merge, InnoDB, and ISAM.

3. Differences between MyISAM and InnoDB

MyISAM : does not support transactions (queries are atomic), uses table‑level locking, stores total row count, consists of three files (.frm, .MYD, .MYI), uses non‑clustered indexes. InnoDB : supports ACID transactions with four isolation levels, uses row‑level locking and foreign‑key constraints, does not store total row count, stores data in a shared or file‑per‑table tablespace, uses clustered primary key indexes and secondary indexes that store the primary key value.

4. InnoDB transaction isolation levels

read uncommitted – reads uncommitted data; read committed – prevents dirty reads; repeatable read – prevents non‑repeatable reads; serializable – highest isolation, fully serial execution.

5. Differences between CHAR and VARCHAR

CHAR has fixed length, padded with spaces, and trailing spaces are trimmed on retrieval; VARCHAR has variable length and stores only the actual characters.

6. Primary key vs. candidate key

Every row is uniquely identified by a primary key; a table has only one primary key. A primary key is also a candidate key. Any candidate key can be chosen as the primary key and can be referenced by foreign keys.

7. Purpose of myisamchk

myisamchk

is used to compress MyISAM tables, reducing disk or memory usage.

8. Effects of defining a TIMESTAMP column

The TIMESTAMP column automatically updates to the current timestamp whenever the row is modified.

9. How to list all indexes of a table

Use the statement

SHOW INDEX FROM <table_name>;

10. Meaning of % and _ in LIKE patterns

% matches zero or more characters; _ matches exactly one character.

11. Converting between Unix and MySQL timestamps

Use UNIX_TIMESTAMP() to convert a MySQL datetime to a Unix timestamp, and FROM_UNIXTIME() to convert a Unix timestamp to a MySQL datetime.

12. Difference between BLOB and TEXT

BLOB stores binary data and comparisons are case‑sensitive; TEXT stores character data and comparisons are case‑insensitive.

13. Difference between mysql_fetch_array and mysql_fetch_object

mysql_fetch_array()

returns a result row as an associative or numeric array; mysql_fetch_object() returns the row as an object.

14. MyISAM storage files

MyISAM tables are stored as three files: .frm (table definition), .MYD (data), and .MYI (indexes).

15. How MySQL optimizes DISTINCT

DISTINCT is transformed into a GROUP BY on all selected columns, often combined with ORDER BY.

16. Displaying the first 50 rows

Use

SELECT * FROM <table> LIMIT 0,50;

17. Maximum number of columns in an index

A standard MySQL table can have up to 16 indexed columns.

18. Difference between NOW() and CURRENT_DATE()

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

19. Non‑standard string types

TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

20. Common SQL functions

CONCAT, FORMAT, CURRDATE, CURRTIME, NOW, MONTH, DAY, YEAR, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, DATEDIFF, SUBTIME, FROMDAYS, etc.

21. Does MySQL support transactions?

By default MySQL runs in autocommit mode, so transactions are not used. When using InnoDB or BDB tables and disabling autocommit (SET AUTOCOMMIT=0), transactions can be managed with COMMIT and ROLLBACK.

22. Recommended column type for monetary values

Use DECIMAL(p,s) (or NUMERIC) to store exact monetary amounts, e.g., salary DECIMAL(9,2) which stores values from –9999999.99 to 9999999.99.

23. MySQL privilege tables

The privilege tables are user, db, table_priv, columns_priv, and host stored in the mysql database.

24. String column types

SET, BLOB, ENUM, CHAR, TEXT.

25. Optimizing a high‑write MySQL system (50k+ inserts per day)

Design a good schema, choose appropriate data types and engines, add indexes, use master‑slave replication, partition tables, add caching (memcached, APC), generate static pages for rarely changing content, and write efficient SQL (select specific columns instead of *).

26. Lock optimization strategies

Read/write separation, segmented locking, minimizing lock hold time, acquiring resources in a consistent order, and avoiding overly fine‑grained locks that cause excessive lock/unlock overhead.

27. Index implementation and optimization

MySQL uses B‑tree (B+Tree) indexes; InnoDB recommends using the auto‑increment primary key as the clustered index and adds pointers between leaf nodes for faster range scans.

28. When an index cannot be used

Leading wildcard in LIKE (e.g., %pattern), OR conditions without indexes on both sides, and implicit type conversions that prevent index usage.

29. Practical MySQL optimization order

1) Optimize SQL statements and indexes; 2) Optimize table structures; 3) Optimize system configuration; 4) Optimize hardware.

30. General database optimization methods

Select appropriate column types, use ENUM for limited values, replace subqueries with JOINs, use UNION instead of temporary tables, employ transactions, lock tables wisely, use foreign keys, create indexes, and tune queries.

31. Differences among index, primary key, unique index, and composite index

Indexes speed up data access; a primary key is a unique index that uniquely identifies a row and can appear only once per table; unique indexes enforce uniqueness; composite (multi‑column) indexes cover multiple columns. Indexes improve read performance but add overhead to writes.

32. What is a transaction?

A transaction is a unit of work consisting of multiple SQL statements that are executed atomically: either all succeed (commit) or all are rolled back on failure. Transactions guarantee atomicity, consistency, isolation, and durability (ACID).

33. Causes of SQL injection and prevention

SQL injection occurs when user input is concatenated into SQL without proper sanitization. Prevent it by using prepared statements, escaping inputs, avoiding dynamic query construction, limiting privileges, and validating/whitelisting input.

34. Choosing appropriate column data types

Prefer numeric types, then date/time, then binary, and finally string types; within the same category, choose the smallest size that meets precision requirements.

35. Date and time storage formats

DATETIME : stores ‘YYYY‑MM‑DD HH:MM:SS’, 8 bytes, timezone‑independent. TIMESTAMP : stores Unix epoch seconds, 4 bytes, range 1970‑2038, auto‑updates on row changes if defined. DATE : stores only date, 3 bytes. TIME : stores only time.

36. Index purpose and impact

Indexes accelerate data retrieval, enforce uniqueness, speed up joins, and improve GROUP BY/ORDER BY performance. However, they consume storage space and slow down INSERT/UPDATE/DELETE because the index must be maintained.

37. Differences among inner join, outer join, and self‑join

Inner join returns rows that satisfy the join condition. Outer join (left/right) returns all rows from one side plus matching rows from the other side, filling missing values with NULL. Self‑join joins a table to itself, often using table aliases.

38. Overview of transaction rollback

Rollback undoes all changes made by a transaction, restoring the database to its state before the transaction began, ensuring atomicity when an error occurs during multi‑table updates.

39. SQL language components

DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), DQL (SELECT).

40. Types of integrity constraints

Entity integrity (primary key uniqueness), domain integrity (data type and range), referential integrity (foreign key consistency), and user‑defined integrity (application‑specific rules).

41. What is a lock?

Locks control concurrent access to data, preventing conflicts and ensuring consistency. MySQL provides row‑level and table‑level locks.

42. What are views and cursors?

A view is a virtual table derived from one or more base tables; it simplifies queries and can provide security. A cursor allows row‑by‑row processing of a result set.

43. What is a stored procedure and how to call it?

A stored procedure is a pre‑compiled set of SQL statements that can be executed repeatedly; it can be invoked via a command object or CALL statement.

44. Simple explanation of the three normal forms

1NF – attributes must be atomic. 2NF – each non‑key attribute must depend on the whole primary key. 3NF – no transitive dependencies; non‑key attributes depend only on the primary key.

45. What is a base table and what is a view?

A base table physically stores data; a view is a derived virtual table that does not store data itself.

46. Advantages of using views

Simplify user queries, provide multiple perspectives on data, offer logical data independence, and enhance security by restricting access to underlying tables.

47. Meaning of NULL

NULL represents an unknown value, distinct from an empty string; any comparison with NULL yields NULL. Use IS NULL to test for NULL.

48. Differences among primary key, foreign key, and index

Primary key uniquely identifies a row, cannot be NULL, and there is only one per table. Foreign key references a primary key in another table, can be NULL, and a table may have many. An index speeds up lookups; it can allow duplicate values (except for unique indexes) and may contain a NULL.

49. How to restrict a column to a specific range of values

Use a CHECK constraint or a trigger to enforce allowed values.

50. Selected SQL optimization techniques

Place the most selective conditions early in the WHERE clause, replace IN with EXISTS, avoid functions on indexed columns, avoid IS NULL on indexed columns, create indexes on columns used in WHERE/ORDER BY, and minimize full‑table scans.