Database indexing techniques

Database indexing techniques ... search data from database using different data structures like trees , hashing and also learn indexing quries

1. Ahmad Ghulam Rasool Data base indexing

2. 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 :

3. 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‘

4. 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 ’

5. 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.

6. What kind of data structure is an index? B- trees are the most commonly used data structures for indexes. T ime efficient D eletions , 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

7. B-Tree

8. Another Data Structure is Hash Table Q ueries 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 . S uppose 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.

9. Hash Table

10. 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.

11. 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

12. What is the cost of having a database index? I t takes up space – and the larger your table , the larger your index. W henever you add, delete, or update rows in the corresponding table, the same operations will have to be done to your index. Drop unused indexes Syntax : Alter table admin drop index searchAge

13. 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) )

14. Cont CREATE TABLE lookup ( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING HASH (id) )

15. Thank you