SQL · Theory

Show Indexes

← All stacks

Theory

79/439

Show Indexes

If you can teach Show Indexes using lookup by roll, you know it. If you only know the heading, you don’t.

Keep Show Indexes small. Isha should finish lookup by roll in a few lines, not a 40-line dump.

Without Show Indexes, a big table gets messy and lookup by roll is hard to trust.

Isha ships Show Indexes in a big table. That is the use case worth saying.

Don’t do this with Show Indexes — indexing a low-cardinality flag only. Interviewers spot it in ten seconds.

Place Show Indexes next to nearby SQL work — PRIMARY KEY(roll) + INDEX is the link.

One breath for Show Indexes, then lookup by roll, then indexing a low-cardinality flag only. Sit down.

Exam tip

For Show Indexes: definition + a big table + one failure.

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.

Short notes

DefShow Indexes — Isha uses it for lookup by roll in a big table.
RuleShow Indexes → PRIMARY KEY(roll) + INDEX.
RememberShow Indexes + an index (a big table).
UseShow Indexes in a big table (lookup by roll).
TrapShow Indexes — indexing a low-cardinality flag only.
ExShow Indexes → lookup by roll.

-- 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

Show Indexes — sample query

Short notes

Questions

Show Indexes

Show Indexes — sample query

Short notes

Questions