Commonly Asked MySQL Interview Questions (2025)

Question: What are indexes in MySQL?

Answer:

In MySQL, an index is a data structure that improves the speed of data retrieval operations on a database table. Indexes are used to quickly locate and access the rows in a table based on the values of one or more columns, without having to scan the entire table. Think of an index like a book’s table of contents, where you can quickly jump to the section you’re interested in without reading every page.

Types of Indexes in MySQL:

MySQL supports several types of indexes, each designed to optimize specific kinds of queries.

1. Primary Key Index

• Definition: A primary key is a special type of unique index where the indexed column(s) cannot contain NULL values. Every table can have only one primary key, and it is used to uniquely identify each row in the table.
• Purpose: Ensures that each record is unique and can be quickly identified using the primary key.
• Example:

  CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
  );

2. Unique Index

• Definition: A unique index ensures that the values in the indexed column(s) are unique. However, unlike the primary key, a unique index allows NULL values unless the column definition explicitly disallows them.
• Purpose: Prevents duplicate entries for the indexed columns.
• Example:

  CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
  );

3. Normal (Non-Unique) Index

• Definition: A normal index is the most basic type of index in MySQL. It does not require the values to be unique and helps to speed up query performance by providing a fast way to search, insert, and delete rows.
• Purpose: Increases performance for queries involving non-unique columns.
• Example:

  CREATE INDEX idx_department ON employees(department);

4. Full-Text Index

• Definition: A full-text index is a special type of index used for full-text searches in MySQL. It allows for indexing large text fields (like TEXT or VARCHAR) and performing searches for words or phrases within the text.
• Purpose: Optimizes searches that involve natural language processing, such as finding rows that match a specific word or phrase.
• Example:

  CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT (content)
  );

5. Spatial Index

• Definition: A spatial index is used for spatial data types such as POINT, LINESTRING, and POLYGON in MySQL. It is used in geographic and location-based queries.
• Purpose: Optimizes queries involving geographic data types and spatial operations like distance calculations and proximity searches.
• Example:

  CREATE TABLE locations (
    id INT PRIMARY KEY,
    coordinates POINT,
    SPATIAL INDEX (coordinates)
  );

6. Composite Index (Multi-Column Index)

• Definition: A composite index is an index on multiple columns, which helps speed up queries that filter based on more than one column. The order of the columns in a composite index matters, and the index will be most effective when the query filters on the columns in the same order as the index.
• Purpose: Speeds up queries that use multiple columns in the WHERE, ORDER BY, or GROUP BY clauses.
• Example:

  CREATE INDEX idx_employee_dept ON employees(employee_id, department);

Benefits of Using Indexes:

• Faster Data Retrieval: Indexes allow MySQL to find rows more efficiently, reducing the amount of data that needs to be scanned.
• Improved Query Performance: Indexes can significantly speed up queries involving SELECT, JOIN, WHERE, ORDER BY, and GROUP BY clauses.
• Efficient Sorting: Indexes can speed up the sorting of data, especially for large tables.

Drawbacks of Indexes:

• Additional Storage: Indexes consume extra disk space because they store the indexed columns’ data.
• Slower Write Operations: Inserting, updating, and deleting rows in a table with indexes can be slower because the index also needs to be updated.
• Overhead: Having too many indexes on a table can reduce the performance of INSERT, UPDATE, and DELETE operations.

When to Use Indexes:

• Use an index when: A column is frequently used in WHERE clauses, JOIN conditions, or as part of an ORDER BY or GROUP BY operation.
• Avoid excessive indexing: Indexes should be used judiciously. Too many indexes can slow down write operations and consume unnecessary storage space.

Example: Creating an Index in MySQL

To create an index on the email column of the employees table, you would use:

CREATE INDEX idx_email ON employees(email);

If you wanted to create a composite index on both first_name and last_name, you would use:

CREATE INDEX idx_name ON employees(first_name, last_name);

Summary:

• Indexes improve the speed of query operations by allowing the database to quickly locate the relevant rows.
• Types of indexes in MySQL include primary key indexes, unique indexes, normal indexes, full-text indexes, spatial indexes, and composite indexes.
• Indexes should be used strategically to enhance performance, but excessive use can lead to increased storage and slower write operations.

Question: Explain the difference between WHERE and HAVING clauses.

Answer:

The WHERE and HAVING clauses are both used to filter records in SQL queries, but they serve different purposes and are used at different stages of the query execution process. Here’s a breakdown of the key differences between them:

1. Purpose and Use:

• WHERE:
  • The WHERE clause is used to filter rows before any grouping is done (i.e., it works on individual rows).
  • It is used to filter the rows in the initial stage of the query execution (before aggregation or grouping).
• HAVING:
  • The HAVING clause is used to filter groups after the GROUP BY operation.
  • It is used to filter the results of an aggregation or grouping of data.

2. When They Are Applied:

• WHERE:
  • The WHERE clause is applied to individual rows before any grouping or aggregation happens.
• HAVING:
  • The HAVING clause is applied to the grouped rows after the GROUP BY operation, which means it can filter aggregated data such as the results of SUM(), AVG(), COUNT(), etc.

3. Conditions:

• WHERE:
  • It can be used with any column to filter rows based on conditions like equality, comparison, ranges, etc.
  • It cannot be used with aggregated functions (like SUM(), COUNT(), AVG(), etc.) because the aggregation hasn’t occurred yet.
• HAVING:
  • It is typically used with aggregate functions to filter groups after they have been formed.
  • You can use HAVING to filter based on conditions involving aggregated values, such as counting how many rows exist in a group or filtering groups based on their sum.

4. Usage Example:

• Using WHERE: The WHERE clause filters rows before any grouping or aggregation:

  SELECT department, salary
  FROM employees
  WHERE salary > 50000;

  In this example, the WHERE clause filters out employees with salaries less than or equal to 50,000 before the query performs any grouping or aggregation.

• Using HAVING: The HAVING clause filters groups after the aggregation:

  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
  HAVING AVG(salary) > 60000;

  In this example, the HAVING clause filters the departments where the average salary is greater than 60,000. This occurs after the GROUP BY operation.

5. Syntax:

• WHERE:
  • The WHERE clause is placed before GROUP BY (if used) and is applied to individual rows.
• HAVING:
  • The HAVING clause is placed after GROUP BY and is applied to groups formed by the GROUP BY operation.

Summary of Differences:

Aspect	WHERE Clause	HAVING Clause
Purpose	Filters individual rows before grouping.	Filters groups after grouping and aggregation.
Used With	Non-aggregated columns.	Aggregated columns (like COUNT(), SUM(), AVG()).
Execution Stage	Applied before GROUP BY and aggregation.	Applied after GROUP BY and aggregation.
Use Case	Filtering rows based on column values.	Filtering groups based on aggregate values.
Example	WHERE salary > 50000	HAVING AVG(salary) > 60000

Key Takeaways:

• WHERE filters data at the row level before grouping or aggregation.
• HAVING filters data at the group level, after the aggregation (using functions like SUM(), AVG(), etc.). It is most useful when you want to apply conditions to aggregated data.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as MySQL interview questions, MySQL interview experiences, and details about various MySQL job positions. Click here to check it out.