Integer types (TINYINT, INT, BIGINT) in MySQL - Time & Space Complexity

Time Complexity: Integer types (TINYINT, INT, BIGINT)

O(n)

Understanding Time Complexity

When working with integer types in MySQL, it's helpful to understand how their size affects performance.

We want to see how the choice of integer type impacts the speed of database operations.

Scenario Under Consideration

Analyze the time complexity of inserting and querying integer values of different sizes.

CREATE TABLE numbers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  small_num TINYINT,
  normal_num INT,
  big_num BIGINT
);

INSERT INTO numbers (small_num, normal_num, big_num) VALUES (127, 2147483647, 9223372036854775807);

SELECT * FROM numbers WHERE normal_num = 1000;
    

This code creates a table with three integer types, inserts a row, and queries by a normal integer.

Identify Repeating Operations

Look at what repeats when the database handles these integers.

• Primary operation: Reading and writing integer values in storage.
• How many times: Once per row inserted or queried, repeated for many rows in large tables.

How Execution Grows With Input

As the number of rows grows, the work to read or write integers grows too.

Input Size (n)	Approx. Operations
10	10 reads/writes
100	100 reads/writes
1000	1000 reads/writes

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 read or write integers grows in a straight line as the number of rows increases.

Common Mistake

[X] Wrong: "Using bigger integer types like BIGINT makes queries much slower because they are huge numbers."

[OK] Correct: The size difference is small for the database, so the time to read or write is mostly about how many rows you have, not the integer size.

Interview Connect

Understanding how data size affects query time helps you design efficient databases and answer questions clearly in interviews.

Self-Check

"What if we added an index on the BIGINT column? How would the time complexity of queries change?"