Indexing Optimization and Query Optimization

Prof.manoj kumar padhi

Indexing Optimization

• Indexing Optimization refers to selecting, designing, and maintaining the best set of indexes in a database to improve data retrieval speed while minimizing storage and update overhead.
• Simple meaning:
• Optimizing indexes is “choosing the right indexes so queries run fast without wasting storage or slowing down inserts/updates”.

​

Goals of Indexing Optimization

• Speed up SELECT queries
• Improve JOIN performance
• Reduce unnecessary full table scans
• Use minimal storage
• Avoid too many or unused indexes

​

Indexing Optimization Techniques

1. Create Indexes on Frequently Searched Columns
2. Use Composite Indexes (Multi-column)
3. Avoid Indexing Small Tables
4. Avoid Over-Indexing
5. Periodically Drop Unused Indexes
6. Choose Right Index Types

Distributed Query Processing and Optimization

• Distributed databases store data across multiple sites/servers.�When you run a query (like SELECT * FROM Student WHERE Marks > 80), the system must figure out how to get the data from different locations efficiently.

1. Distributed Query Processing

• How a query is executed when the required data is stored at different sites.
• What happens during distributed query processing?
• When you send a query:
• Parser → checks syntax
• Query Decomposer → breaks the query into smaller operations
• Optimizer → finds the best way to run those operations across sites
• Local Processors → each site runs its part
• Results are merged and sent to the user

​

Distributed Query Optimization

• It Finding the fastest, cheapest, and most efficient way to execute a distributed query.
• Because in distributed systems:�Communication cost is high�Network is slow�Sending large data between sites is expensive

So optimization tries to reduce:

• Data transfer cost
• Response time
• Network traffic
• Processing cost

​

How Does Distributed Query Optimization Work?

• The optimizer tries different plans and chooses the best one.
• Example:
• To join Student (A) and Marks (B):
• Plan 1: Send Student table (big table) to Site B → Expensive�Plan 2: Send Marks table (small table) to Site A → Faster�Plan 3: Send only the needed rows → Best
• The optimizer chooses Plan 3.

​

Query Optimization

• Query Optimization is the process of selecting the most efficient way to execute a SQL query by analyzing different execution plans and choosing the least-cost one.
• Simple meaning:
• Query Optimization = finding the fastest, cheapest plan to run a query.

​

Goals of Query Optimization

• Minimize CPU usage
• Reduce disk I/O
• Reduce network/data transfer (in distributed DB)
• Reduce response time
• Improve overall system performance

​

Query Optimization Techniques

1. Use Indexes Wisely

• Indexes let the database quickly look up rows instead of scanning the entire table.

​

2. Avoid SELECT *: Choose Only Required Columns

• Using SELECT * impact query performance with large tables or joins. The database engine retrieves every column, even the ones you don’t need which increases memory usage, slows down data transfer, and makes the execution plan more complex. �

3. Limit Rows with WHERE

• Fetching more rows than needed is a common issue. Even if you only use 10 rows in your app, the query might retrieve thousands, slowing things down. Use the WHERE clause to filter data precisely and LIMIT to restrict the number of rows returned.

Example:

SELECT name FROM customers

WHERE country = 'USA'

ORDER BY signup_date DESC

LIMIT 50;

​

Avoid Functions on Indexed Columns�

• Using SQL functions (like UPPER(), LOWER(), DATE()) on indexed columns can prevent the database from using indexes, leading to slower queries.

​

• Bad (pseudocode):
• SELECT * FROM users WHERE UPPER(email) = 'JOHN@GMAIL.COM';
• Good (pseudocode):
• SELECT * FROM users WHERE email = 'john@gmail.com';

​

Use Joins Smartly�

• Use INNER JOIN instead of OUTER JOIN when you don't need unmatched records.

Use Query Rewriting Techniques

• Selection pushdown
• Projection pushdown
• Join reordering
• Remove redundant conditions

​

Indexing Optimization VS Query Optimization