BLOB and binary types in MySQL - Time & Space Complexity

Time Complexity: BLOB and binary types

O(n)

Understanding Time Complexity

When working with BLOB and binary types in MySQL, it's important to understand how the time to read or write data grows as the data size increases.

We want to know how the operations scale when handling larger binary data.

Scenario Under Consideration

Analyze the time complexity of inserting and retrieving binary data using BLOB types.

INSERT INTO images (id, data) VALUES (1, LOAD_FILE('photo.jpg'));

SELECT data FROM images WHERE id = 1;
    

This code inserts a binary file into a BLOB column and then retrieves it by id.

Identify Repeating Operations

Look for repeated work inside these operations.

• Primary operation: Reading or writing the binary data byte by byte or in chunks.
• How many times: The operation repeats once for each byte of the binary data.

How Execution Grows With Input

The time to insert or retrieve grows as the size of the binary data grows.

Input Size (bytes)	Approx. Operations
10 KB	10,240 operations
100 KB	102,400 operations
1 MB	1,048,576 operations

Pattern observation: The operations increase directly with the size of the data.

Final Time Complexity

Time Complexity: O(n)

This means the time grows linearly with the size of the binary data being handled.

Common Mistake

[X] Wrong: "Reading or writing BLOB data takes the same time no matter how big the file is."

[OK] Correct: The time depends on the size of the data because each byte must be processed, so bigger files take longer.

Interview Connect

Understanding how binary data operations scale helps you explain performance in real applications that store images, videos, or files in databases.

Self-Check

What if we changed from BLOB to storing file paths as text? How would the time complexity of retrieval change?