DEV Community

Cover image for Advance Indexing in MYSql and Psql
Ahmed Raza Idrisi
Ahmed Raza Idrisi

Posted on

Advance Indexing in MYSql and Psql

#webdev #backend #database #programming

Perfect 👌 Let’s go deep into Indexing since it’s the heart of database performance.
I’ll keep it detailed but easy to follow with MySQL & PostgreSQL examples.

🔹 Advanced Indexing Explained

Indexes are like the table of contents in a book 📖.

  • Without index → You scan the whole book to find “Chapter 10”.
  • With index → You jump directly to the page number listed.

In databases, indexes speed up SELECT queries by avoiding full table scans.

1. B-Tree Index (Default in MySQL & PostgreSQL)

  • Most common type.
  • Great for equality (=) and range queries (<, >, BETWEEN).
  • Data stored in a balanced tree → lookup is O(log n).

Example (Postgres/MySQL):

CREATE INDEX idx_users_email ON users(email);
Enter fullscreen mode Exit fullscreen mode

Query using it:

SELECT * FROM users WHERE email = 'abc@gmail.com';
Enter fullscreen mode Exit fullscreen mode

👉 Index lets DB jump directly to matching rows instead of scanning all.

2. Hash Index

  • Only good for exact matches (=).
  • Not good for ranges (<, >, BETWEEN).
  • MySQL has them in Memory engine; PostgreSQL supports HASH explicitly.

Example (Postgres):

CREATE INDEX idx_users_username_hash ON users USING HASH(username);
Enter fullscreen mode Exit fullscreen mode

👉 WHERE username = 'john_doe' becomes super fast.

3. GIN Index (Generalized Inverted Index – PostgreSQL only)

  • Used for full-text search and JSONB queries.
  • Stores a mapping from value → rows containing it.

Example: Full-text search

CREATE INDEX idx_post_content_gin ON posts USING GIN(to_tsvector('english', content));
Enter fullscreen mode Exit fullscreen mode

Query:

SELECT * FROM posts WHERE to_tsvector('english', content) @@ to_tsquery('database & scaling');
Enter fullscreen mode Exit fullscreen mode

👉 Useful for search features (like Google inside your DB).

4. GiST Index (Generalized Search Tree – PostgreSQL)

  • More flexible than B-Tree.
  • Supports geospatial queries, ranges, similarity search.

Example (find locations near a point):

CREATE INDEX idx_locations_gist ON locations USING GiST(geom);
Enter fullscreen mode Exit fullscreen mode

Query:

SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(77.5946, 12.9716)::geography, 5000);
Enter fullscreen mode Exit fullscreen mode

👉 Finds all locations within 5km of given coordinates (great for maps).

5. BRIN Index (Block Range Index – PostgreSQL)

  • Very lightweight.
  • Instead of indexing every row, it stores min/max per block of rows.
  • Great for sequential / time-series data.

Example:

CREATE INDEX idx_logs_date_brin ON logs USING BRIN(log_date);
Enter fullscreen mode Exit fullscreen mode

👉 Efficient for queries like:

SELECT * FROM logs WHERE log_date BETWEEN '2025-01-01' AND '2025-01-31';
Enter fullscreen mode Exit fullscreen mode

👉 Works best when data is naturally ordered (like timestamps).

6. Covering Index (a.k.a. Index with INCLUDE columns)

  • Stores extra columns in the index itself.
  • Avoids going back to the table (called “index-only scan”).

Postgres:

CREATE INDEX idx_orders_customer_date
ON orders(customer_id) INCLUDE(order_date, total_amount);
Enter fullscreen mode Exit fullscreen mode

Now:

SELECT customer_id, order_date, total_amount
FROM orders WHERE customer_id = 123;
Enter fullscreen mode Exit fullscreen mode

👉 DB serves query only from index, no table lookup needed.

7. Partial / Filtered Index

  • Index only rows that meet a condition.
  • Saves space & improves performance when you query subsets often.

Postgres:

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
Enter fullscreen mode Exit fullscreen mode

👉 Queries on active users become lightning fast, without indexing inactive ones.

8. Expression Index

  • Index on a function or expression.
  • Great when queries always apply transformations.

Postgres:

CREATE INDEX idx_lower_email ON users(LOWER(email));
Enter fullscreen mode Exit fullscreen mode

Now:

SELECT * FROM users WHERE LOWER(email) = 'abc@gmail.com';
Enter fullscreen mode Exit fullscreen mode

👉 Uses index instead of scanning all rows.

🔹 How to Know Which Index to Use?

  • Exact match lookup → Hash index (Postgres) or B-Tree.
  • Range queries (>, <, BETWEEN) → B-Tree.
  • Text search / JSONB search → GIN.
  • Geospatial / similarity → GiST.
  • Time-series / ordered data → BRIN.
  • Only certain rows → Partial index.
  • Always lowercase/transform → Expression index.

🔹 Important Notes

  • Indexes speed up reads but slow down writes (inserts/updates/deletes need to update indexes).
  • Too many indexes = slower writes + more storage.
  • Always use EXPLAIN or EXPLAIN ANALYZE to see if your query uses the index.

✅ Summary:
Indexes are like shortcuts — different types exist for different problems.
Mastering them = 80% of database optimization as a database engineer.

Top comments (0)