SQL · Theory

SUBSTRING_INDEX()()

← All stacks

Theory

170/439

SUBSTRING_INDEX()()

SUBSTRING_INDEX()() is a SQL idea you prove with lookup by roll, not with a slogan.

SUBSTRING_INDEX()() order — meaning → PRIMARY KEY(roll) + INDEX → one limit. Dumping ten features helps nobody.

SUBSTRING_INDEX()() exists so Riya can keep lookup by roll correct in a big table.

SUBSTRING_INDEX()() shows up in a big table. Name lookup by roll, not “a real-world scenario”.

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

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

Viva for SUBSTRING_INDEX()(): what it is → PRIMARY KEY(roll) + INDEX → the mistake (indexing a low-cardinality flag only).

Exam tip

Say SUBSTRING_INDEX()() in one breath, then PRIMARY KEY(roll) + INDEX, then indexing a low-cardinality flag only.

SUBSTRING_INDEX()() — sample query

-- SUBSTRING_INDEX()()
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;

SUBSTRING_INDEX()(): create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefSUBSTRING_INDEX()() — Riya uses it for lookup by roll in a big table.
RuleSUBSTRING_INDEX()() → PRIMARY KEY(roll) + INDEX.
RememberSUBSTRING_INDEX()() + the query planner (a big table).
UseSUBSTRING_INDEX()() in a big table (lookup by roll).
TrapSUBSTRING_INDEX()() — indexing a low-cardinality flag only.
ExSUBSTRING_INDEX()() → lookup by roll.

-- SUBSTRING_INDEX()() 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;

SUBSTRING_INDEX()()

SUBSTRING_INDEX()() — sample query

Short notes

Questions

SUBSTRING_INDEX()()

SUBSTRING_INDEX()() — sample query

Short notes

Questions