Page 107 - CITS - Computer Software Application -TT
P. 107

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
   102   103   104   105   106   107   108   109   110   111   112