Selecting specific columns in MySQL - Time & Space Complexity

Time Complexity: Selecting specific columns

O(n)

Understanding Time Complexity

When we ask for specific columns in a database, we want to know how the work grows as the table gets bigger.

We want to see how the time to get results changes when we select only some columns instead of all.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT name, email
FROM users
WHERE active = 1;
    

This query gets only the name and email columns from all active users in the users table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning each row in the users table to check if active = 1.
• How many times: Once for each row in the table.

How Execution Grows With Input

As the number of rows grows, the database checks more rows to find active users.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

Pattern observation: The work grows directly with the number of rows in the table.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line as the table gets bigger.

Common Mistake

[X] Wrong: "Selecting fewer columns makes the query run much faster in terms of time complexity."

[OK] Correct: The main time cost is checking each row, not the number of columns selected. Selecting fewer columns helps with data size but does not change how many rows are checked.

Interview Connect

Understanding how selecting columns affects query time helps you explain database efficiency clearly and confidently.

Self-Check

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

Practice

1. What does the SQL statement SELECT name, age FROM users; do?

easy

A. It updates the name and age columns in the users table.

B. It retrieves all columns from the users table.

C. It deletes the name and age columns from the users table.

D. It retrieves only the name and age columns from the users table.

Solution

1. Step 1: Understand the SELECT statement

   The SELECT keyword is used to choose specific columns from a table.

2. Step 2: Identify the columns listed

   The columns listed after SELECT are name and age, so only these will be retrieved.

3. Final Answer:

   It retrieves only the name and age columns from the users table. -> Option D

4. Quick Check:

   Select specific columns = name, age [OK]

Hint: SELECT columns by listing names separated by commas [OK]

Common Mistakes:

• Thinking SELECT * returns specific columns
• Confusing SELECT with DELETE or UPDATE
• Forgetting commas between column names

2. Which of the following is the correct syntax to select the columns id and email from a table named customers?

easy

A. SELECT id email FROM customers;

B. SELECT id, email FROM customers;

C. SELECT (id, email) FROM customers;

D. SELECT * FROM customers WHERE id, email;

Solution

1. Step 1: Recall correct SELECT syntax

   Columns must be separated by commas without parentheses.

2. Step 2: Check each option

   SELECT id, email FROM customers; uses commas correctly: SELECT id, email FROM customers;.

3. Final Answer:

   SELECT id, email FROM customers; -> Option B

4. Quick Check:

   Columns separated by commas = SELECT id, email FROM customers; [OK]

Hint: Use commas between columns, no parentheses [OK]

Common Mistakes:

• Omitting commas between columns
• Using parentheses around columns
• Misusing WHERE clause for column selection

3. Given the table products with columns product_id, name, and price, what will be the output of this query?

SELECT name, price FROM products WHERE price > 100;

medium

A. Only the name and price of products with price greater than 100.

B. All columns for products with price greater than 100.

C. Only the product_id and price of products with price greater than 100.

D. Syntax error due to missing commas.

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only name and price columns.

2. Step 2: Analyze the WHERE clause

   It filters rows where price > 100.

3. Final Answer:

   Only the name and price of products with price greater than 100. -> Option A

4. Quick Check:

   Select specific columns with condition = Only the name and price of products with price greater than 100. [OK]

Hint: SELECT columns listed, WHERE filters rows [OK]

Common Mistakes:

• Assuming all columns are returned
• Confusing WHERE with column selection
• Thinking missing commas cause error here

4. Identify the error in this query:

SELECT id name FROM employees;

medium

A. SELECT keyword is misspelled.

B. Table name is incorrect.

C. Missing comma between column names.

D. No error, query is correct.

Solution

1. Step 1: Check column list syntax

   Columns must be separated by commas. Here, id and name are missing a comma.

2. Step 2: Verify other parts

   Table name employees and SELECT keyword are correct.

3. Final Answer:

   Missing comma between column names. -> Option C

4. Quick Check:

   Comma missing between columns = Missing comma between column names. [OK]

Hint: Separate columns with commas to avoid syntax errors [OK]

Common Mistakes:

• Forgetting commas between columns
• Assuming space works as separator
• Misreading error as table or keyword issue

5. You have a table orders with columns order_id, customer_id, order_date, and total. You want to get only the order_id and total for orders placed in 2023. Which query is correct?

hard

A. SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023;

B. SELECT order_id total FROM orders WHERE order_date = 2023;

C. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%';

D. SELECT * FROM orders WHERE order_date = 2023;

Solution

1. Step 1: Select specific columns

   The query must select order_id and total only, so SELECT order_id, total is correct.

2. Step 2: Filter orders from 2023

   Using WHERE YEAR(order_date) = 2023 correctly filters orders placed in 2023.

3. Step 3: Check other options

   SELECT order_id total FROM orders WHERE order_date = 2023; misses comma and uses wrong date filter. SELECT order_id, total FROM orders WHERE order_date LIKE '2023%'; uses LIKE which may work but is less precise. SELECT * FROM orders WHERE order_date = 2023; selects all columns, not specific ones.

4. Final Answer:

   SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; -> Option A

5. Quick Check:

   Select columns + correct year filter = SELECT order_id, total FROM orders WHERE YEAR(order_date) = 2023; [OK]

Hint: Use YEAR() to filter dates, commas to select columns [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong date filter syntax
• Selecting all columns instead of specific ones