"Just add an index and the query will be faster, right?"

Not necessarily.

Indexes can dramatically improve database performance, but every index also has a cost. It consumes storage, increases write overhead, and needs to be maintained when data changes.

Good index design starts with one question:

What queries does the application actually run?

How Does an Index Speed Up a Query?

Without a suitable index, the database may need to scan many rows to find matching data.

For example:

SELECT *
FROM users
WHERE email = 'dev@example.com';

An index on email gives the database another access path:

CREATE INDEX idx_users_email
ON users(email);

For many relational database workloads, a B-tree index is a common starting point for equality and range queries.

Composite indexes are useful when queries frequently filter on multiple columns:

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

The index may then help queries such as:

SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'paid';

But creating an index doesn't guarantee that the optimizer will use it.

That's an important distinction.

Start With Query Patterns

Good index design starts with the queries, not the table definition.

Look at your application's real workload and ask:

1. Which queries run most frequently?

A query executed thousands of times per minute deserves more attention than a query executed once a day.

2. Which columns appear in WHERE conditions?

Frequently filtered columns are common candidates for indexes.

3. Which columns appear in ORDER BY or JOIN conditions?

These may also influence index design.

4. Are multiple columns commonly filtered together?

If the workload frequently contains:

WHERE user_id = ?
AND status = ?

a composite index may be more useful than two unrelated single-column indexes.

The key is to design indexes around real query patterns.

Why More Indexes Can Make Performance Worse

Indexes speed up reads, but they aren't free.

Every additional index can increase the work required for:

INSERT
UPDATE
DELETE

Consider a write-heavy table with several indexes.

When a new row is inserted, the database doesn't just write the table data. It may also need to update multiple index structures.

That means an index that improves one SELECT query could increase overall write latency.

Indexes also consume disk space and require maintenance.

So the goal isn't:

"Create as many indexes as possible."

The goal is:

Create the indexes that provide meaningful value for your workload.

Watch Out for Low-Selectivity Columns

Not every frequently filtered column is a good index candidate.

Consider a column such as:

status = 'active'

If almost every row has the same value, an index may provide limited benefit because the condition doesn't narrow the result set very much.

The same principle applies to other low-cardinality columns.

Index usefulness depends on factors such as:

• Data distribution

• Selectivity

• Table size

• Query frequency

• Query shape

• Read/write ratio

There is no universal rule that says a particular column type should always be indexed.

Don't Skip the Execution Plan

This is where database performance tuning becomes more systematic.

Don't create an index and immediately assume the query is faster.

Check the execution plan.

You want to know:

• Is the index being used?

• How many rows does the optimizer expect?

• How many rows are actually examined?

• Is the database still performing a full scan?

• Did the index improve the actual execution time?

A useful tuning sequence is:

Refresh statistics → inspect the execution plan → identify the bottleneck → then consider indexes or SQL changes.

Why refresh statistics first?

Because the query optimizer relies on statistics to estimate costs and choose an execution plan.

If statistics are stale, the optimizer may make a poor decision—even when the "right" index already exists.

A Practical Index Tuning Example

Suppose this query is slow:

SELECT *
FROM orders
WHERE customer_id = 1001
  AND status = 'paid';

Instead of immediately creating several indexes, start by checking the current execution plan.

Then refresh statistics if necessary.

If the optimizer still chooses an inefficient plan, evaluate whether this composite index matches the actual workload:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Run the query again and compare the execution plan and actual performance.

This creates a much better tuning loop:

Slow query
    ↓
Check statistics
    ↓
Inspect execution plan
    ↓
Understand the access path
    ↓
Evaluate index design
    ↓
Test again

How GBase Database Approaches Index Tuning

The same principle applies to GBase Database.

Don't treat indexes as the first solution to every slow query.

With GBase Database, a practical performance tuning workflow should begin by making sure the optimizer has accurate statistics, then checking the execution plan, and only afterward deciding whether SQL or index changes are necessary.

This matters because an index is useful only when it matches the workload and the optimizer can make good use of it.

In other words:

Don't ask "What index can I add?"

Ask:

"Why is the optimizer choosing this execution plan?"

That question usually leads to better database performance decisions.

Index Design Checklist

Before creating an index, ask:

• Is this query frequent or business-critical?

• How selective is the indexed column?

• Is the table read-heavy or write-heavy?

• Could an existing index already support the query?

• Would a composite index better match the query pattern?

• What does the execution plan show?

• Are the statistics current?

• Did the index actually improve performance?

If you can't answer these questions, you're probably not ready to add the index.

Final Takeaway

Indexes are powerful database performance tools—but they're not magic switches.

Good index design starts with query patterns, not with the assumption that every column needs an index.

The practical order is:

Understand the workload → refresh statistics → inspect the execution plan → evaluate indexes → measure the result.

Whether you're working with GBase Database or another relational database, this approach helps prevent unnecessary indexes, reduce write overhead, and make database tuning much more predictable.

The best index isn't the one you can create. It's the one your workload actually needs.