Skip to content
sqladvanced

SQL Performance Optimization

Indexes, execution plans and query optimization

7 questions

By EZ4Code Team

1. Which type of query is a B+ tree index best suited for?

Equality queries, range queries, and prefix sorting
Full table scans
Any LIKE query
JSON field queries
Explanation: B+ tree indexes support equality, range, and ordered queries; LIKE '%xx' with a leading wildcard cannot use an index (does not satisfy the leftmost prefix).

2. What does the leftmost prefix principle mean for a composite index (a, b, c)?

Query conditions must start with a to use the index
Must use a, b, c simultaneously
Can only use a
Can start from any column
Explanation: Composite indexes follow the leftmost prefix principle: you can use (a), (a,b), (a,b,c), but cannot skip a and use b or c with this index.

3. What does EXPLAIN do?

View the execution plan of SQL, evaluating whether it uses an index and the number of rows scanned
Execute SQL
Optimize SQL
Validate syntax
Explanation: EXPLAIN (or EXPLAIN ANALYZE) shows the query execution plan, including access type, indexes used, estimated row count, etc., used for tuning.

4. Which of the following usually cannot use an index?

WHERE name LIKE '%abc'
WHERE id = 1
WHERE id BETWEEN 1 AND 10
WHERE id IN (1,2,3)
Explanation: A leading wildcard LIKE '%abc' does not satisfy the leftmost prefix and cannot use a B+ tree index; prefix matching LIKE 'abc%' can use an index.

5. What is a covering index?

An index that contains all columns needed by the query, without needing to go back to the table
Covers all tables
Covers all rows
Covers all databases
Explanation: A covering index contains all columns involved in the query, can return results directly from the index, avoiding table lookups, significantly improving performance.

6. What is the main problem with SELECT *?

Transmits unnecessary columns, cannot use covering indexes, increases IO and memory overhead
Syntax error
Cannot be used with multiple tables
Always slower
Explanation: SELECT * returns all columns, transmitting redundant data, cannot use covering indexes, increasing network/memory/IO overhead; you should select only needed columns.

7. What is a common practice to avoid index invalidation?

Do not use functions or implicit type conversions on indexed columns
Build indexes on all columns
Use SELECT * as much as possible
Delete all indexes
Explanation: Using functions, operations, or implicit type conversions on indexed columns invalidates the index and should be avoided; also do not over-index (affects write performance).

More sql Quizzes

SQL Basics Quiz

beginner

SELECT, WHERE, ORDER BY, GROUP BY, JOINs, and fundamental SQL queries.

SQL Advanced Quiz

advanced

Subqueries, window functions, CTEs, indexes, and advanced SQL techniques.

SQL Join Queries

intermediate

INNER/LEFT/RIGHT/FULL JOIN

SQL Window Functions

advanced

ROW_NUMBER, RANK, LAG, etc.