🔍 What is Indexing?
Indexing in a Database Management System (DBMS) is used to speed up the retrieval of data from tables without scanning the entire table every time a query is run.
🔍 Main Purpose: Faster Data Retrieval
Imagine a huge book (like a phonebook). If you had to find a name without the index, you'd read every page — that’s a full table scan.
But with an index, you can jump directly to the section — just like in a real book. That’s what indexing does in a database.
💡 Why Indexing is Important:
Reason | Explanation |
|---|---|
🔹 Improves Query Speed | Fetches data faster by reducing the number of disk I/Os. |
🔹 Reduces Full Table Scans | Prevents scanning all rows for common queries. |
🔹 Boosts Performance in Joins | Indexes on foreign keys make joins faster. |
🔹 Optimizes WHERE Conditions | Makes searches based on conditions like |
🔹 Supports Sorting and Grouping | Helps in faster execution of |
📌 How Index Works (Basic Idea)
An index is a separate data structure (like a B-Tree or Hash Table) that holds a key (like a column value) and a pointer to the actual location of the data row.
Example:
Consider this table:
ID | Name | Age |
|---|---|---|
1 | Alice | 24 |
2 | Bob | 30 |
3 | Carol | 27 |
If you create an index on Age, the index might look like:
[24] → Row 1
[27] → Row 3
[30] → Row 2So when you run:
SELECT * FROM students WHERE age = 27;The DBMS goes directly to Row 3 using the index — instead of checking all rows.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>How Indexes Work — Try It Yourself</title>
<style>
.infographic-indexsandbox {
--ink: #1a1a1a;
--ink-soft: #5c5c5c;
--paper: #ffffff;
--paper-alt: #f0ece4;
--accent: #2563eb;
--accent-soft:#e3ebfd;
--line: #ddd6c9;
--good: #16a34a;
--good-soft: #dcfce7;
--bad: #dc2626;
--bad-soft: #fee2e2;
--display-font: Georgia, 'Iowan Old Style', serif;
--body-font: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
--mono-font: 'SF Mono', Consolas, monospace;
background: var(--paper);
color: var(--ink);
font-family: var(--body-font);
max-width: 980px;
margin: 0 auto;
box-sizing: border-box;
overflow-x: hidden;
}
.infographic-indexsandbox[data-theme="dark"] {
--ink: #e8e6e1;
--ink-soft: #9a968c;
--paper: #121212;
--paper-alt: #1e1e26;
--accent: #5b8def;
--accent-soft:#202942;
--line: #2c313d;
--good: #4ade80;
--good-soft: #123420;
--bad: #f87171;
--bad-soft: #3a1414;
}
.infographic-indexsandbox * { box-sizing: border-box; }
.ix-head { text-align: center; margin-bottom: 0.25rem; }
.ix-head h2 {
font-family: var(--display-font);
font-size: 1.95rem;
margin: 0 0 0.5rem;
}
.ix-head p {
color: var(--ink-soft);
font-size: 1rem;
margin: 0 auto;
max-width: 600px;
line-height: 1.55;
}
.ix-head code { background: var(--paper-alt); border-radius: 4px; padding: 0.05rem 0.35rem; }
.ix-query {
margin-top: 1.5rem;
display: flex;
align-items: center;
justify-content: center;
gap: 0.6rem;
flex-wrap: wrap;
}
.ix-query-text {
font-family: var(--mono-font);
font-size: 0.92rem;
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 8px;
padding: 0.6rem 0.9rem;
}
.ix-query select {
font: inherit;
font-family: var(--mono-font);
font-size: 0.92rem;
padding: 0.5rem 0.6rem;
border: 1px solid var(--line);
border-radius: 6px;
background: var(--paper);
color: var(--accent);
font-weight: 700;
}
.ix-body {
display: grid;
grid-template-columns: 1fr 1fr;
gap: 1.5rem;
margin-top: 1.75rem;
min-width: 0;
}
@media (max-width: 720px) { .ix-body { grid-template-columns: 1fr; } }
.ix-panel {
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 10px;
padding: 1.1rem;
min-width: 0;
}
.ix-panel-title { font-family: var(--display-font); font-size: 1rem; margin: 0 0 0.75rem; }
.ix-table-scroll { overflow-x: auto; -webkit-overflow-scrolling: touch; min-width: 0; }
table.ix-table {
width: 100%;
min-width: 220px;
border-collapse: collapse;
background: var(--paper);
border-radius: 8px;
font-size: 0.85rem;
}
table.ix-table th, table.ix-table td {
border: 1px solid var(--line);
padding: 0.45rem 0.6rem;
text-align: left;
white-space: nowrap;
transition: background 0.2s, color 0.2s;
}
table.ix-table th { background: var(--accent); color: #fff; font-weight: 700; font-size: 0.72rem; }
table.ix-table tr.is-checking td { background: var(--accent-soft); }
table.ix-table tr.is-match td { background: var(--good-soft); color: var(--good); font-weight: 700; }
.ix-list { display: flex; flex-direction: column; gap: 0.35rem; }
.ix-entry {
font-family: var(--mono-font);
font-size: 0.82rem;
background: var(--paper);
border: 1px solid var(--line);
border-radius: 6px;
padding: 0.4rem 0.6rem;
transition: background 0.2s, color 0.2s, border-color 0.2s;
}
.ix-entry.is-checking { background: var(--accent-soft); border-color: var(--accent); }
.ix-entry.is-eliminated { opacity: 0.3; }
.ix-entry.is-match { background: var(--good-soft); color: var(--good); border-color: var(--good); font-weight: 700; }
.ix-btn-row { display: flex; gap: 0.6rem; flex-wrap: wrap; margin-top: 1.5rem; justify-content: center; }
.ix-btn {
font: inherit;
font-size: 0.85rem;
background: var(--accent);
color: #fff;
border: none;
border-radius: 7px;
padding: 0.6rem 1.1rem;
cursor: pointer;
}
.ix-btn:hover { opacity: 0.9; }
.ix-btn:disabled { opacity: 0.4; cursor: not-allowed; }
.ix-btn.is-secondary { background: var(--paper); color: var(--ink); border: 1px solid var(--line); }
.ix-log-scroll { overflow-x: auto; }
.ix-log {
margin-top: 1.1rem;
background: var(--ink);
color: #d4e2ff;
border-radius: 8px;
padding: 0.75rem 0.9rem;
font-family: var(--mono-font);
font-size: 0.78rem;
line-height: 1.65;
min-height: 2rem;
}
.infographic-indexsandbox[data-theme="dark"] .ix-log { background: #05070a; }
.ix-log-line { opacity: 0; animation: ix-line-in 0.25s ease forwards; }
@keyframes ix-line-in { to { opacity: 1; } }
.ix-log-placeholder { color: #7c8aa8; font-style: italic; }
.ix-scoreboard {
display: flex;
gap: 1rem;
justify-content: center;
margin-top: 1.5rem;
flex-wrap: wrap;
}
.ix-score {
background: var(--paper-alt);
border: 1px solid var(--line);
border-radius: 8px;
padding: 0.7rem 1.2rem;
text-align: center;
min-width: 160px;
}
.ix-score-label { font-size: 0.75rem; color: var(--ink-soft); text-transform: uppercase; letter-spacing: 0.04em; }
.ix-score-value { font-family: var(--mono-font); font-size: 1.5rem; margin-top: 0.2rem; color: var(--accent); }
.ix-score.is-winner .ix-score-value { color: var(--good); }
@media (prefers-reduced-motion: reduce) {
.infographic-indexsandbox *, .infographic-indexsandbox *::before, .infographic-indexsandbox *::after {
transition-duration: 0.001ms !important;
animation-duration: 0.001ms !important;
}
}
</style>
</head>
<body>
<div class="infographic-indexsandbox" id="indexsandbox">
<div class="ix-query">
<span class="ix-query-text">SELECT * FROM students WHERE age =</span>
<select id="ixAgeSelect"></select>
</div>
<div class="ix-body">
<div class="ix-panel">
<p class="ix-panel-title">🎓 Students table</p>
<div class="ix-table-scroll">
<table class="ix-table" id="ixTable"></table>
</div>
</div>
<div class="ix-panel">
<p class="ix-panel-title">🌲 Index on Age (sorted)</p>
<div class="ix-list" id="ixList"></div>
</div>
</div>
<div class="ix-btn-row">
<button class="ix-btn" id="ixRunScan" type="button">Run without index (table scan)</button>
<button class="ix-btn" id="ixRunIndex" type="button">Run with index (jump straight there)</button>
<button class="ix-btn is-secondary" id="ixReset" type="button">Reset</button>
</div>
<div class="ix-log-scroll">
<div class="ix-log" id="ixLog"><span class="ix-log-placeholder">Run a search above to see it happen step by step...</span></div>
</div>
<div class="ix-scoreboard" id="ixScoreboard">
<div class="ix-score" id="ixScoreScan">
<div class="ix-score-label">Table scan</div>
<div class="ix-score-value">–</div>
</div>
<div class="ix-score" id="ixScoreIndex">
<div class="ix-score-label">Index lookup</div>
<div class="ix-score-value">–</div>
</div>
</div>
</div>
<script>
(function () {
const wrapper = document.getElementById('indexsandbox');
const bodyAttr = document.body.getAttribute('data-darkmode');
let dark;
if (bodyAttr === 'true') {
dark = true;
} else if (bodyAttr === 'false') {
dark = false;
} else {
const match = document.cookie.match(/(?:^|;\s*)darkMode=([^;]+)/);
dark = match ? match[1] === 'true' : false;
}
if (dark) wrapper.setAttribute('data-theme', 'dark');
})();
(function () {
const students = [
{ id: 1, name: 'Alice', age: 24 },
{ id: 2, name: 'Bob', age: 30 },
{ id: 3, name: 'Carol', age: 27 },
{ id: 4, name: 'David', age: 19 },
{ id: 5, name: 'Emma', age: 35 },
{ id: 6, name: 'Frank', age: 22 },
{ id: 7, name: 'Grace', age: 41 },
{ id: 8, name: 'Henry', age: 28 }
];
const index = students.map(s => ({ age: s.age, rowId: s.id })).sort((a, b) => a.age - b.age);
const table = document.getElementById('ixTable');
const list = document.getElementById('ixList');
const log = document.getElementById('ixLog');
const select = document.getElementById('ixAgeSelect');
const scoreScan = document.querySelector('#ixScoreScan .ix-score-value');
const scoreIndex = document.querySelector('#ixScoreIndex .ix-score-value');
const scoreScanCard = document.getElementById('ixScoreScan');
const scoreIndexCard = document.getElementById('ixScoreIndex');
const runScanBtn = document.getElementById('ixRunScan');
const runIndexBtn = document.getElementById('ixRunIndex');
const resetBtn = document.getElementById('ixReset');
function renderTable() {
let html = '<thead><tr><th>ID</th><th>Name</th><th>Age</th></tr></thead><tbody>';
students.forEach(s => {
html += '<tr data-row="' + s.id + '"><td>' + s.id + '</td><td>' + s.name + '</td><td>' + s.age + '</td></tr>';
});
html += '</tbody>';
table.innerHTML = html;
}
function renderList() {
list.innerHTML = index.map((e, i) =>
'<div class="ix-entry" data-idx="' + i + '">[' + e.age + '] → Row ' + e.rowId + '</div>'
).join('');
}
function fillSelect() {
select.innerHTML = students
.slice()
.sort((a, b) => a.age - b.age)
.map(s => '<option value="' + s.age + '">' + s.age + '</option>')
.join('');
select.value = '27';
}
function clearLog() {
log.innerHTML = '';
}
function appendLog(text) {
if (log.querySelector('.ix-log-placeholder')) log.innerHTML = '';
const line = document.createElement('div');
line.className = 'ix-log-line';
line.textContent = text;
log.appendChild(line);
log.scrollTop = log.scrollHeight;
}
function clearHighlights() {
table.querySelectorAll('tr').forEach(tr => tr.classList.remove('is-checking', 'is-match'));
list.querySelectorAll('.ix-entry').forEach(el => el.classList.remove('is-checking', 'is-eliminated', 'is-match'));
}
function setButtonsDisabled(disabled) {
runScanBtn.disabled = disabled;
runIndexBtn.disabled = disabled;
}
runScanBtn.addEventListener('click', () => {
clearHighlights();
clearLog();
setButtonsDisabled(true);
const target = Number(select.value);
let i = 0;
let count = 0;
function step() {
if (i >= students.length) {
setButtonsDisabled(false);
return;
}
const row = students[i];
const tr = table.querySelector('tr[data-row="' + row.id + '"]');
table.querySelectorAll('tr').forEach(t => t.classList.remove('is-checking'));
tr.classList.add('is-checking');
count++;
appendLog('Row ' + row.id + ': age = ' + row.age + (row.age === target ? ' ✓ match!' : ' ≠ ' + target));
if (row.age === target) {
tr.classList.remove('is-checking');
tr.classList.add('is-match');
scoreScan.textContent = count + ' row' + (count === 1 ? '' : 's') + ' checked';
markWinner();
setButtonsDisabled(false);
return;
}
i++;
setTimeout(step, 380);
}
step();
});
runIndexBtn.addEventListener('click', () => {
clearHighlights();
clearLog();
setButtonsDisabled(true);
const target = Number(select.value);
let low = 0, high = index.length - 1, count = 0;
function step() {
if (low > high) {
setButtonsDisabled(false);
return;
}
const mid = Math.floor((low + high) / 2);
const entry = index[mid];
count++;
list.querySelectorAll('.ix-entry').forEach(el => el.classList.remove('is-checking'));
const el = list.querySelector('.ix-entry[data-idx="' + mid + '"]');
el.classList.add('is-checking');
appendLog('Check middle of range: [' + entry.age + '] → Row ' + entry.rowId + (entry.age === target ? ' ✓ match!' : entry.age < target ? ' too low, search right half' : ' too high, search left half'));
if (entry.age === target) {
el.classList.remove('is-checking');
el.classList.add('is-match');
// fade out entries never visited
list.querySelectorAll('.ix-entry').forEach(e2 => {
if (e2 !== el) e2.classList.add('is-eliminated');
});
const tr = table.querySelector('tr[data-row="' + entry.rowId + '"]');
tr.classList.add('is-match');
appendLog('→ Follows pointer straight to Row ' + entry.rowId + ' in the table. No scanning needed.');
scoreIndex.textContent = count + ' comparison' + (count === 1 ? '' : 's') + ' + 1 jump';
markWinner();
setButtonsDisabled(false);
return;
} else if (entry.age < target) {
el.classList.add('is-eliminated');
for (let k = low; k < mid; k++) {
const e2 = list.querySelector('.ix-entry[data-idx="' + k + '"]');
if (e2) e2.classList.add('is-eliminated');
}
low = mid + 1;
} else {
el.classList.add('is-eliminated');
for (let k = mid + 1; k <= high; k++) {
const e2 = list.querySelector('.ix-entry[data-idx="' + k + '"]');
if (e2) e2.classList.add('is-eliminated');
}
high = mid - 1;
}
setTimeout(step, 480);
}
step();
});
function markWinner() {
scoreScanCard.classList.remove('is-winner');
scoreIndexCard.classList.remove('is-winner');
const scanText = scoreScan.textContent;
const idxText = scoreIndex.textContent;
if (scanText === '–' || idxText === '–') return;
const scanNum = parseInt(scanText, 10);
const idxNum = parseInt(idxText, 10);
if (idxNum < scanNum) scoreIndexCard.classList.add('is-winner');
else if (scanNum < idxNum) scoreScanCard.classList.add('is-winner');
}
resetBtn.addEventListener('click', () => {
clearHighlights();
clearLog();
log.innerHTML = '<span class="ix-log-placeholder">Run a search above to see it happen step by step...</span>';
scoreScan.textContent = '–';
scoreIndex.textContent = '–';
scoreScanCard.classList.remove('is-winner');
scoreIndexCard.classList.remove('is-winner');
setButtonsDisabled(false);
});
renderTable();
renderList();
fillSelect();
})();
</script>
</body>
</html>.demo {
font-family: system-ui, sans-serif;
padding: 16px;
}
.demo h1 {
color: #2563eb;
margin: 0 0 8px;
}
.demo p {
color: #4b5563;
margin: 0;
}document.querySelector('.demo h1')?.addEventListener('click', () => {
alert('Hello from live preview!');
});🧱 Types of Indexes
Type | Use Case |
|---|---|
Primary Index | Based on the primary key. |
Secondary Index | Based on non-primary attributes. |
Clustered Index | Sorts actual data rows. Only one per table. |
Non-clustered Index | Separate from data, can be multiple per table. |
Composite Index | Based on multiple columns. |
❗ Drawbacks of Indexing
Drawback | Reason |
|---|---|
🚫 Slower Insert/Update/Delete | Indexes must be updated each time data changes. |
🧠 Extra Storage Required | Index files take up space. |
⚠️ Over-indexing Hurts Performance | Too many indexes slow down write operations. |
✅ When to Use Indexing
Use indexing when:
You frequently search by a column (e.g.,
WHERE,JOIN,ORDER BY).You work with large tables.
You have performance issues due to full table scans.
🧠 Summary
Feature | Role of Indexing |
|---|---|
🔎 Query Speed | Makes data retrieval faster |
📦 Storage | Requires extra space for index files |
✍️ Write Speed | May slow down INSERT/UPDATE/DELETE |
⚙️ Structure | Based on B-Trees or Hash Tables |