database indexing techniques
TRANSCRIPT
![Page 1: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/1.jpg)
Ahmad Ghulam Rasool
Data base indexing
![Page 2: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/2.jpg)
Data Base Indexing• A database index is a data structure that
improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
• Example :
![Page 3: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/3.jpg)
How do database indexes work?
• Now, let’s say that we want to run a query to find all the details of any employees who are named ‘Jesus’? So, we decide to run a simple query like this:
• Example :• SELECT * FROM Employee WHERE Employee_Name = 'Jesus‘
![Page 4: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/4.jpg)
What would happen without an index on the table?
• Well, the database software would literally have to look at every single row in the Customers table to see if the customerName for that row is ‘Jesus’
![Page 5: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/5.jpg)
How a database index can help performance ?
• The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.
![Page 6: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/6.jpg)
What kind of data structure is an index?
• B- trees are the most commonly used data structures for indexes.
• Time efficient • Deletions, and insertions can all be done
in logarithmic time• No need to sort data just use algorithm to
get sorted data like inorder,postorder etc• No need to search whole table
![Page 7: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/7.jpg)
B-Tree
![Page 8: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/8.jpg)
Another Data Structure is Hash Table
• Queries that compare for equality to a string can retrieve values very fast if they use a hash index.
• Example : SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’
• Hash tables are not sorted data structures.• Suppose you want to find out all of the employees who
are less than 40 years old. How could you do that with a hash table index? Well, it’s not possible because a hash table is only good for looking up key value pairs.
![Page 9: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/9.jpg)
Hash Table
![Page 10: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/10.jpg)
What exactly is inside a database index?
• A database index does not store the values in the other columns of the same table.
• Example: Age and Employee_Address column values are not also stored in the index.
• An index stores a pointer to the table row.
![Page 11: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/11.jpg)
How to create an index in SQL• Syntax: CREATE INDEX name_indexON
Employee (Employee_Name)
• How to create a multi-column index in SQL• Syntax: CREATE INDEX name_indexON
Employee (Employee_Name, Employee_Age)• ALTER TABLE tbl_name ADD PRIMARY KEY
(column_list): This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL
![Page 12: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/12.jpg)
What is the cost of having a database index?
• It takes up space – and the larger your table, the larger your index.
• Whenever you add, delete, or update rows in the corresponding table, the same operations will have to be done to your index.
• Drop unused indexesSyntax: Alter table admin drop index searchAge
![Page 13: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/13.jpg)
Index quries• SHOW INDEX FROM table_name• ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);• ALTER TABLE testalter_tbl DROP PRIMARY KEY;• CREATE TABLE lookup( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING BTREE (id))
![Page 14: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/14.jpg)
Cont
• CREATE TABLE lookup( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING HASH (id))
![Page 15: Database indexing techniques](https://reader036.vdocuments.us/reader036/viewer/2022062903/58ed8f831a28ab2c7b8b45e9/html5/thumbnails/15.jpg)
Thank you