Find top SQL resources from global universities, cool projects, and learning materials for data analytics.
Admin: @coderfun
Useful links: heylink.me/DataAnalytics
Promotions: @love_data
Post #2811
458
SELECT
region,
COUNT(*)
FROM orders
GROUP BY region;
An index on "region" may sometimes help, depending on the database and execution plan.
But indexes don't automatically make every aggregation faster.
For large analytical workloads, the database may choose another strategy.
9️⃣ Primary Keys and Indexes
Primary keys are commonly backed by an index or equivalent structure.
For example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
The database generally creates an index-like structure to enforce primary-key uniqueness and support efficient lookups.
The exact implementation varies by database system.
🔟 Unique Index
A unique index prevents duplicate values in the indexed key.
Example:
CREATE UNIQUE INDEX idx_customers_email
ON customers(email);
Now duplicate email values are not allowed, subject to the database's NULL semantics.
For example:
• customer1@example.com
• customer2@example.com
can exist.
But two identical non-NULL values generally cannot.
A "UNIQUE" constraint is another way to enforce uniqueness and may be implemented using a unique index depending on the database.
1️⃣1️⃣ Single-Column Index
An index can contain one column.
CREATE INDEX idx_orders_customer
ON orders(customer_id);
This is called a single-column index.
Useful when queries frequently search using:
WHERE customer_id = ...1️⃣2️⃣ Composite Index
An index can also contain multiple columns.
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
This is called a composite index or multi-column index.
It can be useful for queries such as:
SELECT *
FROM orders
WHERE customer_id = 101
AND order_date >= DATE '2026-01-01';
1️⃣3️⃣ Column Order Matters
This is one of the most important concepts with composite indexes.
Suppose:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
The index starts with:
customer_idand then:
order_dateThis means the index is particularly useful for queries that use the leading column:
WHERE customer_id = 101and often:
WHERE customer_id = 101
AND order_date >= DATE '2026-01-01';
But a query filtering only:
WHERE order_date >= DATE '2026-01-01'may not benefit from this index in the same way.
The exact behavior depends on the database engine and optimizer.
1️⃣4️⃣ The Leftmost Prefix Concept
For:
CREATE INDEX idx_orders
ON orders(customer_id, order_date, status);
Think of the index as:
customer_id
↓
order_date
↓
status
Queries using the leading columns can often take better advantage of the index.
For example:
WHERE customer_id = 101or:
WHERE customer_id = 101
AND order_date >= DATE '2026-01-01'
can be good candidates.
But:
WHERE status = 'Completed'doesn't start with the leading indexed column.
The optimizer may therefore choose another access method.
1️⃣5️⃣ Indexes and LIKE
Consider:
SELECT *
FROM customers
WHERE customer_name LIKE 'Rah%';
An index may be usable for a prefix search in some database systems.
But:
WHERE customer_name LIKE '%Rah';or:
WHERE customer_name LIKE '%Rah%'; can make ordinary B-tree index usage less effective because the pattern begins with a wildcard.
Database-specific indexing features can change this behavior.
1️⃣6️⃣ Indexes and Functions
Consider:
