Database.What types of indexes exist?

🧱 1. B-tree Index (Balanced Tree)

• 🔹 Most common index type (used by MySQL, PostgreSQL, Oracle, etc.)
• 🔹 Efficient for equality (=) and range queries (<, >, BETWEEN, etc.)

✅ Good for:

WHERE age = 30
WHERE name BETWEEN 'A' AND 'F'

🪜 2. Composite Index (Multi-column)

• 🔹 Index on two or more columns: (A, B)
• 🔹 Useful when queries filter or sort on multiple columns
• 🔹 Only works well when filtering starts with the left-most column

✅ Supports:
WHERE A = ?
WHERE A = ? AND B = ?
❌ Not efficient for WHERE B = ? alone

🔑 3. Unique Index

• 🔹 Enforces uniqueness of values in one or more columns
• 🔹 Automatically created for primary keys and unique constraints

✅ Ensures no duplicates in:

email, username, passport_number, etc.

⚙️ 4. Hash Index

• 🔹 Fast for exact match lookups (=)
• ❌ Not useful for range queries
• 🔹 Used internally in some engines like MySQL MEMORY table

✅ Good for:

WHERE user_id = 123

❌ Not for:

WHERE user_id > 100

🔍 5. Full-Text Index

• 🔹 Designed for searching large text fields (e.g., blog posts, comments)
• 🔹 Supports natural language search (MATCH ... AGAINST)
• 🔹 Available in MySQL, PostgreSQL (with GIN or TSVECTOR)

✅ Example:

SELECT * FROM articles
WHERE MATCH(content) AGAINST ('climate change');

🌐 6. Spatial Index (R-tree / GiST)

• 🔹 Used for geographic or spatial data (e.g., GPS coordinates)
• 🔹 Supports queries like “find all restaurants within 5 km”

✅ Example:

WHERE ST_Distance(location, POINT(55.75, 37.62)) < 5000;

🧠 7. Bitmap Index

• 🔹 Efficient for low-cardinality columns (e.g., gender, status, yes/no)
• 🔹 Often used in data warehouses for analytics
• 🔹 Available in Oracle and some column-store DBs

🧪 8. Covering Index

• 🔹 A composite index that contains all the columns used by a query
• 🔹 Allows the database to answer the query using only the index, skipping table access

✅ Faster performance, especially for read-heavy workloads

📋 Summary Table:

Index Type	Best For	Not Good For
B-tree	Range and equality searches	Very large text or spatial
Hash	Equality (=) searches	Range queries (<, >)
Full-text	Text search	Structured field filtering
Composite	Multi-column filtering	Queries using only middle columns
Unique	Enforcing uniqueness	High-insert workloads (may slow inserts)
Bitmap	Low-cardinality analytics	Frequent writes
Spatial	Geolocation / maps	Regular string/number columns