Page 104 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

How to Create Indexes
Creating indexes in a relational database involves using the CREATE INDEX statement.

The syntax may vary slightly between different database management systems (DBMS)
Syntax:
CREATE INDEX [index_name]
ON [table_name] ([column_name]);
Query:

CREATE INDEX product_category_index
ON product (category);
When you run this query, it will experience a prolonged execution time compared to a standard query. This is
because the database is scanning through a substantial 12 million rows and constructing a new ‘category’ index
from the ground up, a process that takes approximately 4 minutes.
Now, let’s assess how the performance of the original query improves after the implementation of indexing.

SELECT COUNT(*)
FROM product
WHERE category = ‘electronics’;
You’ll observe a significant improvement in the query’s speed this time. It is likely to complete in a much shorter
timeframe, perhaps around 400 milliseconds.
Moreover, the positive impact of indexing on ‘category’ extends beyond just queries explicitly involving this
condition. To illustrate, let’s consider a scenario where queries involve additional conditions beyond ‘category’—
even these queries will experience enhanced performance due to the indexing on ‘category’.
SELECT COUNT(*)
FROM product
WHERE category = ‘electronics’

AND product_subcategory = ‘headphone’;
In this case, the query’s execution time is expected to be reduced compared to its normal duration, perhaps
completing in around 600 milliseconds. The database can efficiently locate all ‘electronics’ products using the
index, resulting in a smaller set of records. Subsequently, it can then identify ‘headphones’ from this narrowed-
down set in the usual manner.
Now, let’s explore the impact of changing the order of conditions in the ‘WHERE’ clause.

SELECT COUNT(*)
FROM product
WHERE product_subcategory = ‘headphone’
AND category = ‘electronics’;

Types of Indexing
Exploring the realm of database indexing involves delving into two primary types
1 Clustered Index
• A clustered index stands as the unique index for a table, employing the primary key to structure the data
within that table. Unlike a non-clustered index, a clustered index doesn’t require explicit declaration; instead,
it is automatically generated when the primary key is defined. By default, the clustered index utilizes the
ascending order of the primary key for organization.

91

CITS : IT&ITES - Computer software application - Lesson 18 - 36