Database indexing techniques

Database indexing techniques

• 2.
  • A databaseindex 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.
  • Now, let’ssay 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.
  • Well, thedatabase 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.
  • The wholepoint 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.
  • B- treesare 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
• 8.
  • Queries thatcompare 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.
• 10.
  • A databaseindex 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.
  • Syntax: CREATEINDEX 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.
  • It takesup 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 indexes Syntax: Alter table admin drop index searchAge
• 13.
  • SHOW INDEXFROM 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 TABLElookup ( id INT NOT NULL, name CHAR(20), PRIMARY KEY USING HASH (id) )