Fredy Santos
Data Analyst and SQL & Excel storytelling Specialist
New to Contra
Fredy is building their profile!
Is Your Database Running or Crawling? The Power of Indexing ⚡
Imagine walking into a library with 1 million books and no catalog. To find one specific title, you’d have to check every single shelf. That is exactly what your database does when you forget to create an Index.
🔍 The Concept: The Library Analogy
In SQL, when we run a query without an index, the engine performs a Full Table Scan. It reads every single row to find the data you need. As your business grows from 1,000 rows to 1,000,000, your '5-second report' suddenly takes 5 minutes.
1. The "Before" (The Table Scan)
Without an index, the database engine is working overtime. High CPU usage, high memory consumption, and frustrated users. It’s brute-force searching.
2. The "After" (The Index Seek)
By creating a B-Tree Index on frequently filtered columns (like CustomerID or OrderDate), we create a 'Table of Contents.' The database jumps straight to the data it needs. The result? Query times drop from seconds to milliseconds.
💡 Why this makes you a better Consultant:
Infrastructure Optimization: You aren't just asking for a bigger, more expensive server; you are making the current one work smarter.
Scalability: You are preparing the client’s data architecture to handle 10x the volume without breaking.
User Experience: Fast data leads to fast decisions. No stakeholder likes a spinning loading wheel.
🛠 The Verdict
Stop: Indexing every single column (this slows down INSERT and UPDATE operations).
Start: Identifying the 'High-Traffic' columns in your WHERE and JOIN clauses and indexing them strategically.
0
41
Using SQL on the Northwind database, I performed a quick investigation about the carriers that are most used to ship to the US, getting as a result important information about the amount of orders delivered by carrier and the average these are billed from us, this investigation helps to analyze and determine strategies to select the best and cheapest carrier, increasing profits by this way.
0
83