Can You Choose the Right MySQL Index? 5 Quiz Questions Explained

Preface

MySQL indexes are commonly used, but how well do you actually understand them? Below are several small quiz questions to test your knowledge, with answers and explanations provided later.

Test Questions

Question 1

Is the following index suitable for this query?

CREATE INDEX tbl_idx ON tbl (date_column)

SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017

Options:

A Very suitable B Not suitable

Question 2

Is the following index suitable for this query?

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

Options:

A Very suitable B Not suitable

Question 3

Is the following index suitable for these two queries?

CREATE INDEX tbl_idx ON tbl (a, b)

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1

SELECT *
  FROM tbl
 WHERE b = 1

Options:

A Very suitable B Not suitable

Question 4

Is the following index suitable for this query?

CREATE INDEX tbl_idx ON tbl (text)

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Options:

A Very suitable B Not suitable

Question 5

Consider the index and query below, then add a new condition b = 1 to the query. How will the new query affect performance?

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Options:

A Both queries have identical performance B Cannot determine due to insufficient information C The second query is slower D The second query is faster

Answers and Explanation

Question 1

CREATE INDEX tbl_idx ON tbl (date_column)

SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017

Answer: B Not suitable

The index is ineffective because a function is applied to the indexed column. A more efficient approach is:

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-01-01'
   AND date_column <  DATE'2018-01-01'

Question 2

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

Answer: A Very suitable

This index efficiently supports both the WHERE clause and the ORDER BY operation.

Question 3

CREATE INDEX tbl_idx ON tbl (a, b)

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1

SELECT *
  FROM tbl
 WHERE b = 1

Answer: B Not suitable

The index only covers the first query; the second query cannot use the index efficiently. Changing the index to CREATE INDEX tbl_idx ON tbl (b, a) would address this.

Question 4

CREATE INDEX tbl_idx ON tbl (text)

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Answer: A Very suitable

Although LIKE uses a wildcard, it is at the end of the pattern, allowing the index to be applied.

Question 5

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Answer: C The second query is slower

The first query can be satisfied entirely by the index (index covering), requiring no table access and delivering excellent performance. The second query must access the table to filter on b = 1, resulting in slower execution.

Summary

The five short index‑usage questions illustrate common pitfalls: applying functions to indexed columns, ordering by indexed columns, covering multiple predicates, using prefix wildcards with LIKE, and the performance impact of adding non‑indexed conditions. Understanding these nuances helps you design more efficient MySQL queries.