Menene index a cikin SQL? Yaushe za ku yi amfani da shi da yaushe ba?

Question

Accepted Answer

An **index** wani tsarin data (galibi **B-tree**) wanda ke ba da damar jiya ta gida sabuwa jeri da sauri **ba tare da neman tebur duka** — kamar jiya ta littafi. Yana ƙara saurin neman abubuwa, tacewa, haɗi, da jera akan gindin index(s), a faɗin waje na ajiya da ruɗewa rubuce-rubuce.

## Yadda index ya taimaka

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

Ba tare da index, jiya ta gida na yi **full table scan** (duba kowane jeri). Da yake akwai, yana yawo a B-tree don nemo jeri masu daidai a cikin lokaci mai aiki logaritmik — canji tsakanin neman milyoyin jeri da ƴan duba kaɗan.

## Lokacin da za a yi amfani da indexes

```text
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
```

## Lokacin da BA a yi amfani da indexes (matakai-matakai)

```text
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
  → too many indexes SLOW DOWN writes significantly
```

Indexes ba su da kyau: suna **cin ajiya** da **ƙera rube rubuce** (kowane gyara dole ya sabunta indexes). Over-indexing ya ɓata aikin rubuce-rubuce.

## Nau'ikan index masu juna da sauransu

```sql
CREATE INDEX idx_users_country_name ON users(country, name);   -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)

CREATE UNIQUE INDEX ...      -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
```

## Me ya matukar mahimmanci

Indexes shine ɗaya daga cikin muhimman jigogi a cikin aiki na jiya — sune babbar kayan aiki don saurin neman, da fahimtar yaushe da yadda za a yi amfani da su shine mahimman ilimi ga kowa yana aiki da jiya a jimillar.

Gidije mahimmanci shine abin ya juyo: ba tare da indexes, buhatun neman wanda ke tacewa, haɗi, ko jera akan gindin suna buƙatar **full table scans** (duba kowane jeri — kamannin aiki a lokacin data ƙaruwa), duk da haka indexes suna buɗar matakin jere saurin — galibi canji tsakanin neman zuwatar milisekonda da sekoci ko mintuna.

Fahimta **lokacin da za a yi index** (gindin WHERE da aka fi neman, JOIN/foreign-key gindin, ORDER BY gindin, ƴan keɓantaccen gindin) shine tushe ga aiki na neman.

Fara daidai shi ne fahimta **matakai-matakai da lokacin da ba a yi index**: indexes suna cin ajiya da, mahimmanci, **ƙera rubuce-rubuce** (kowane INSERT/UPDATE/DELETE dole ya sabunta duk indexes), saboda haka over-indexing ya ɓata aikin rubuce-rubuce, da indexes suna ƙara ƙarfi kaɗan a kan littattafan kanana ko ƴan keɓantaccen gindin.

Daidaita haka — index abubuwan da ake neman duk da kuma ba over-index — shine skaiko mahimmanci.

Sanin game da **composite indexes** (gindin masu yawa, inda tsarin gindin ya matukar aiki ga wane neman suke taimakawa) yana bace ilimin amfani index.

Tunda aiki na neman mahimmanci ne ga ayyukan da suke aiki, kuma tun da daidaitaccen indexing (indexes daidai akan gindin daidai, ma'auni saurin karanta da farashi rubuce da ajiya) shine abin da ya fi tasiri optimizeation jiya, ƙwarewar indexes — yadda suke aiki, yaushe za a yi amfani da su, ƙera rubuce-rubuce da ajiya, da composite indexes — shine mahimmanci, babbar-babbar ilimi don jiya masu aiki da ɗaya daga cikin mahimman jigogi a neman jiya.