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?"

Practice

1. What is the main difference between ENUM and SET types in MySQL?

easy

A. ENUM allows multiple values, SET allows only one value.

B. ENUM stores numbers, SET stores strings.

C. ENUM allows only one value from a list, SET allows multiple values.

D. ENUM is for dates, SET is for text.

Solution

1. Step 1: Understand ENUM type

   ENUM restricts a column to one value from a predefined list.

2. Step 2: Understand SET type

   SET allows storing zero or more values from a predefined list in one column.

3. Final Answer:

   ENUM allows only one value, SET allows multiple values -> Option C

4. Quick Check:

   ENUM = single, SET = multiple [OK]

Hint: Remember: ENUM = one choice, SET = many choices [OK]

Common Mistakes:

• Confusing which type allows multiple values
• Thinking ENUM stores numbers only
• Assuming SET is for single values

2. Which of the following is the correct syntax to create a column named colors that can store multiple colors using SET type?

easy

A. colors SET('red', 'green', 'blue')

B. colors ENUM('red', 'green', 'blue')

C. colors SET['red', 'green', 'blue']

D. colors ENUM['red', 'green', 'blue']

Solution

1. Step 1: Recall correct syntax for SET

   The correct syntax uses parentheses and single quotes: SET('value1', 'value2', ...)

2. Step 2: Identify correct option

   colors SET('red', 'green', 'blue') uses SET with parentheses and quoted values correctly.

3. Final Answer:

   colors SET('red', 'green', 'blue') -> Option A

4. Quick Check:

   SET uses parentheses and quotes [OK]

Hint: SET and ENUM use parentheses, not brackets [OK]

Common Mistakes:

• Using square brackets instead of parentheses
• Mixing ENUM syntax with SET
• Omitting quotes around values

3. Given the table products with a column features SET('wifi', 'bluetooth', 'gps'), what will be the result of this query?

SELECT features FROM products WHERE FIND_IN_SET('gps', features);

medium

A. Returns rows where 'gps' is one of the features.

B. Returns rows where features exactly equal 'gps'.

C. Returns all rows regardless of features.

D. Syntax error due to FIND_IN_SET usage.

Solution

1. Step 1: Understand FIND_IN_SET function

   FIND_IN_SET('gps', features) checks if 'gps' is one of the comma-separated values in the SET column.

2. Step 2: Interpret the query result

   The query returns rows where 'gps' is included in the features set, not just exact matches.

3. Final Answer:

   Returns rows where 'gps' is one of the features. -> Option A

4. Quick Check:

   FIND_IN_SET finds value inside SET [OK]

Hint: FIND_IN_SET checks if value is in SET list [OK]

Common Mistakes:

• Thinking FIND_IN_SET matches exact whole column
• Assuming syntax error with FIND_IN_SET
• Confusing SET with ENUM behavior

4. Consider this table creation:

CREATE TABLE users (status ENUM('active', 'inactive', 'pending'));

Which of the following INSERT statements will cause an error?

medium

A. INSERT INTO users (status) VALUES (NULL);

B. INSERT INTO users (status) VALUES ('active');

C. INSERT INTO users (status) VALUES ('pending');

D. INSERT INTO users (status) VALUES ('blocked');

Solution

1. Step 1: Check allowed ENUM values

   The ENUM allows only 'active', 'inactive', or 'pending'. 'blocked' is not allowed.

2. Step 2: Analyze each INSERT

   Inserting 'blocked' will cause an error because it is not in the ENUM list. NULL is allowed if column permits it.

3. Final Answer:

   INSERT with 'blocked' causes error -> Option D

4. Quick Check:

   Only ENUM values allowed, others error [OK]

Hint: Only ENUM list values allowed, others cause error [OK]

Common Mistakes:

• Assuming NULL always causes error
• Thinking any string is allowed
• Confusing ENUM with SET behavior

5. You want to store user preferences for notifications: email, sms, and push. Users can choose any combination. Which column type and definition is best to store this efficiently?

hard

A. Use ENUM('email', 'sms', 'push') to store one preference.

B. Use SET('email', 'sms', 'push') to store multiple preferences.

C. Use VARCHAR(255) and store comma-separated values.

D. Use three separate BOOLEAN columns for each preference.

Solution

1. Step 1: Identify requirement for multiple choices

   Users can select any combination of preferences, so multiple values per row are needed.

2. Step 2: Choose appropriate data type

   SET type allows storing multiple predefined values efficiently in one column.

3. Step 3: Compare other options

   ENUM allows only one value, VARCHAR wastes space and needs parsing, separate BOOLEANs increase columns.

4. Final Answer:

   Use SET('email', 'sms', 'push') for multiple preferences -> Option B

5. Quick Check:

   Multiple choices = SET type [OK]

Hint: Multiple options? Use SET, not ENUM [OK]

Common Mistakes:

• Using ENUM for multiple selections
• Storing comma-separated strings manually
• Adding many boolean columns unnecessarily