Backend interview prep

Start

MySQL Basics Interview Questions

Master MySQL fundamentals with interview-focused questions covering architecture, storage engines, databases, tables, SQL execution flow, constraints, ACID, indexing, and production best practices.

Introduction

MySQL is one of the world's most popular Relational Database Management Systems (RDBMS). It is widely used for enterprise applications, banking systems, e-commerce platforms, ERP solutions, content management systems, and cloud-native applications.

MySQL stores data in tables with predefined schemas and uses SQL (Structured Query Language) to manage and query data.

Companies using MySQL include

  • Netflix
  • Uber
  • Airbnb
  • Facebook (early versions)
  • Booking.com
  • Shopify
  • PayPal
  • GitHub

Understanding MySQL fundamentals is essential for every Java Backend Developer, Database Engineer, and Solution Architect.

MySQL Architecture

flowchart LR

Application --> JDBCDriver --> MySQLServer

MySQLServer --> Parser

Parser --> Optimizer

Optimizer --> StorageEngine

StorageEngine --> DataFiles

1. What is MySQL?

Answer

MySQL is an

  • Open Source RDBMS
  • Client-Server Database
  • SQL-based Database
  • ACID-compliant Database (using InnoDB)

It stores data in tables consisting of rows and columns.

2. Why is MySQL popular?

Reasons include

  • Open Source
  • High Performance
  • Reliable
  • ACID Support
  • Easy Administration
  • Large Community
  • Cloud Support
  • Cross Platform

3. What is an RDBMS?

RDBMS stands for

Relational Database Management System

Data is stored in

  • Tables
  • Rows
  • Columns

Relationships are maintained using keys.

SQL vs MySQL

SQL MySQL
Query Language Database Software
Standard Product
Used by Many Databases Uses SQL
Defines Commands Executes Commands

4. What is SQL?

SQL

Structured Query Language

used for

  • Querying
  • Inserting
  • Updating
  • Deleting
  • Creating Objects

5. What are Storage Engines?

Storage Engines determine

how MySQL stores

and retrieves data.

Popular engines

  • InnoDB
  • MyISAM
  • MEMORY
  • CSV

MySQL Components

flowchart LR

Client --> SQLParser --> Optimizer --> StorageEngine --> Disk

6. What is a Database?

A Database is a logical container for tables.

Example

BankDB

HRDB

InventoryDB

7. What is a Table?

A Table stores related information.

Example

Employee

Columns

EmployeeId

Name

Salary

8. What is a Row?

A Row represents one record.

Example

EmployeeId Name Salary
100 John 90000

9. What is a Column?

A Column defines an attribute.

Example

EmployeeId

Name

Salary

Department

Database Structure

flowchart LR

Database --> EmployeeTable

EmployeeTable --> Rows

Rows --> Columns

10. What is a Primary Key?

Primary Key

  • Uniquely identifies a row
  • Cannot be NULL
  • Must be Unique

Example

CREATE TABLE Employee(

EmployeeId INT PRIMARY KEY

);

11. What is a Foreign Key?

Foreign Key creates relationships between tables.

Example

DepartmentId

REFERENCES

Department(Id)

12. Why are Foreign Keys important?

They ensure

  • Referential Integrity
  • Data Consistency
  • Relationship Validation

13. What is AUTO_INCREMENT?

Automatically generates numeric values.

Example

EmployeeId

INT AUTO_INCREMENT

14. What are Constraints?

Rules enforced on data.

Common constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK
  • DEFAULT

15. What is NOT NULL?

Column cannot store NULL.

Example

Name

VARCHAR(100)

NOT NULL

16. Difference between NULL and Empty String?

NULL Empty String
Unknown Value Known Empty Value
No Data Zero-Length Text

17. What are MySQL Data Types?

Numeric

  • INT
  • BIGINT
  • DECIMAL

String

  • CHAR
  • VARCHAR
  • TEXT

Date

  • DATE
  • DATETIME
  • TIMESTAMP

Others

  • JSON
  • BLOB

18. Difference between CHAR and VARCHAR?

CHAR VARCHAR
Fixed Length Variable Length
Faster Saves Storage
Pads Spaces No Padding

19. What is Character Set?

Defines

how characters

are stored.

Common

utf8mb4

supports full Unicode.

20. What is Collation?

Defines

sorting

and

comparison

rules.

Example

utf8mb4_unicode_ci

21. What is SQL Execution Flow?

Query

↓

Parser

↓

Optimizer

↓

Execution Engine

↓

Storage Engine

↓

Result

SQL Execution Flow

flowchart LR

SQL --> Parser --> Optimizer --> Execution --> StorageEngine --> Result

22. What is the Query Optimizer?

Optimizer selects

the best execution plan

using

  • Indexes
  • Statistics
  • Cost Estimation

23. What is an Execution Plan?

Execution Plan shows

how MySQL executes a query.

Example

EXPLAIN

SELECT *

FROM Employee;

24. What is ACID?

ACID ensures reliable transactions.

Properties

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Covered in detail later.

25. What is an Index?

Index speeds up

query execution

by avoiding full table scans.

26. What is a Full Table Scan?

Reads every row.

Very slow

for large tables.

27. What is Client-Server Architecture?

Clients

↓

JDBC Driver

↓

MySQL Server

↓

Database

Applications connect over TCP/IP.

Client-Server Architecture

flowchart LR

JavaApplication --> JDBC --> MySQLServer --> Database

28. Banking Example

Tables

Customers

Accounts

Transactions

Related using

Primary Keys

and

Foreign Keys.

29. E-Commerce Example

Tables

Products

Orders

Customers

Payments

30. HR Example

Tables

Employee

Department

Salary

Attendance

31. Advantages of MySQL

  • Open Source
  • Reliable
  • Mature Ecosystem
  • ACID Support
  • Replication
  • Partitioning
  • High Performance
  • Excellent Tooling

32. Limitations

  • Vertical scaling limitations
  • Complex sharding
  • Fewer built-in analytics than specialized systems
  • Schema changes may require planning

33. Common mistakes

  • Missing indexes
  • Poor normalization
  • Selecting unnecessary columns
  • Ignoring execution plans
  • Using SELECT * unnecessarily

Enterprise Best Practices

  • Use InnoDB for OLTP applications.
  • Always define primary keys.
  • Create foreign keys where appropriate.
  • Use proper data types.
  • Use utf8mb4 character set.
  • Analyze execution plans using EXPLAIN.
  • Create indexes for frequently searched columns.
  • Avoid unnecessary SELECT *.
  • Normalize data appropriately.
  • Monitor slow queries.

Quick Revision

Topic Key Point
Database Collection of Tables
Table Collection of Rows
Row Single Record
Column Attribute
Primary Key Unique Identifier
Foreign Key Relationship
Storage Engine Data Storage Layer
SQL Query Language
EXPLAIN Execution Plan
ACID Transaction Properties
Index Faster Search

Interview Tips

Interviewers frequently ask

  • What is MySQL?
  • Difference between SQL and MySQL.
  • What is an RDBMS?
  • Explain Primary Key and Foreign Key.
  • What is AUTO_INCREMENT?
  • CHAR vs VARCHAR.
  • What is a Storage Engine?
  • Explain SQL execution flow.
  • What is EXPLAIN?
  • Give a real-world MySQL schema example.

Always explain that MySQL is a relational database that stores structured data using tables and SQL, while emphasizing its client-server architecture, storage engines, ACID compliance (through InnoDB), and optimization features used in enterprise applications.

Summary

MySQL is a powerful, open-source relational database management system used to build scalable, secure, and reliable enterprise applications. It organizes data into databases, tables, rows, and columns while providing powerful SQL capabilities for data manipulation and retrieval.

Understanding MySQL architecture, storage engines, constraints, indexes, SQL execution flow, and core relational concepts forms the foundation for advanced topics such as transactions, locking, replication, partitioning, performance tuning, and production database management.