Page 107 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

FROM product

WHERE category = ‘electronics’;
The database operates through three key steps:
1 Firstly: It navigates to the non-clustered index (in this case, ‘product_category_index’), pinpointing the column
entry you searched for (e.g., category = ‘electronics’) using the efficient binary search method.

2 Secondly: It seeks the address of the corresponding row in the main table that corresponds to the identified
column entry.
3 Finally: It accesses that specific row in the main table, retrieving additional column values as needed for your
query (e.g., product_name, price).
It’s important to note that a non-clustered index involves an additional step compared to a clustered index—it
requires finding the address and then going to the corresponding row in the main table. This additional step
makes non-clustered indexes relatively slower than their clustered counterparts.
CREATE TABLE demo(

id INT NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(35) NOT NULL,
age INT NOT NULL,
PRIMARY KEY(id)

);
/* Index on First Name */
CREATE INDEX demo_fname ON demo (first_name);
/* Will tell us whether our query uses the intended index */

explain SELECT * FROM demo WHERE first_name = “Donald” \G
Output
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo

partitions: NULL
type: ref
possible_keys: demo_fname
key: demo_fname

key_len: 27
ref: const
rows: 1
filtered: 100.00 Extra: NULL
To harness the index’s benefits, it’s crucial to isolate the column, ensuring it’s not incorporated into a function or
expression.

94

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