Show Indexes — SQL Technical Interview Questions & Answers

Show Indexes

Show Indexes

  • SHOW INDEX FROM students; lists indexes, columns, uniqueness, cardinality. Key_name PRIMARY is the PK.
  • EXPLAIN still answers ‘will this query use it?’. SHOW INDEX answers ‘what exists?’.
  • Trap: looking at cardinality 2 on a boolean and expecting miracles.
  • On the example next to this theory: Show Indexes: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: SHOW INDEX FROM students;

Code Examples for Show Indexes

Show Indexes — sample query

-- Show Indexes
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Show Indexes: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Show Indexes to an Interviewer

How do you list indexes?

SHOW INDEX FROM students;