ENUM and SET types in MySQL - Time & Space Complexity

Time Complexity: ENUM and SET types

O(n)

Understanding Time Complexity

We want to understand how the time it takes to work with ENUM and SET types changes as the data grows.

Specifically, how does MySQL handle these types when searching or filtering data?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE TABLE products (
  id INT PRIMARY KEY,
  color ENUM('red', 'green', 'blue', 'yellow'),
  tags SET('new', 'sale', 'popular', 'limited')
);

SELECT * FROM products WHERE color = 'red';
SELECT * FROM products WHERE FIND_IN_SET('sale', tags);
    

This code creates a table with ENUM and SET columns and queries rows filtering by these types.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning rows to compare ENUM or SET values.
• How many times: Once per row in the table during the query.

How Execution Grows With Input

As the number of rows grows, the database checks each row's ENUM or SET value to find matches.

Input Size (n)	Approx. Operations
10	10 comparisons
100	100 comparisons
1000	1000 comparisons

Pattern observation: The number of operations grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to find matching rows grows in a straight line as the table gets bigger.

Common Mistake

[X] Wrong: "ENUM and SET types make searches instant no matter the table size."

[OK] Correct: Even though ENUM and SET store values efficiently, searching still checks each row unless there is an index.

Interview Connect

Understanding how ENUM and SET types affect query time helps you explain data design choices clearly and shows you know how databases handle different data types.

Self-Check

"What if we add an index on the ENUM column? How would the time complexity change?"