SQL with Jupyter Notebook Tutorial

46 / 61
Previous Index Next  

Indexes

What are indexes?

  • Indexes on a table are similar to indexes found at the end of a book to help you reach the required topics/chapters quickly without flipping through the whole book.

  • Indexes are data structures storing the pointer to the actual physical location of records on the disk.

  • These index data structures themselves are stored on disk to make them persistent.

  • Indexes are generally created on columns which are most likely to be part of the SELECT expression, WHERE clauses and ORDER BY expression.

  • If such a query is run whose all columns are present in one or more indexes, then the query may not need to touch the tables at all. Rather, the query will get all the data from indexes only.

  • SELECT becomes faster because of indexes.SELECT expression.

  • INSERT/UPDATE/DELETE become slower because of indexes as indexes also need to be updated accordingly in runtime.SELECT expression.

Indexes can have of 2 types of data structures:

  • Btree (Ordered binary tree, defaul type) -> Good for =, !=, >=, >=, BETWEEN, LIKE, IN operators.
  • Hash (Not ordered, Hash values based, more like key-value pair) -> Good for = operator.

Indexes can be -> Unique / Non-Unique

  • Unique -> Primary key of a table or any other set of one or more columns which would identify a unique record in the table. For example - Emp Id OR Customer Id and Bank Account No

  • Non-Unique -> Any set of one or more columns to speed up the query on non-unique columns. For example - Index on First name and Last name.

Creating Indexes:

Here, we are creating an index with the btree algorithm. The name of the index is dep_name_idx.

%%sql
create unique index dep_name_idx using btree on deptt_<<your lab username>> (depname);

Here, we are just creating an index named dep_name_idx_2 without mentioning the indexing algorithm. In such cases, the default algorithm - btree - is used.

%%sql
create unique index dep_name_idx_2 on deptt_<<your lab username>> (depname);

--In both of the above examples, name of index is given by user.

%%sql
create index emp_names on emp_<<your lab username>> (empfname, emplname);

--In the above example, the name of the index will be generated by the system

%%sql
alter table emp_<<your lab username>> add index (empfname, emplname) using hash;

Dropping index:

%%sql
drop index emp_names on emp_<<your lab username>>;

or 

%%sql    
alter table emp_<<your lab username>> drop index emp_names;

Viewing indexes on a table:

%%sql
show index from emp_<<your lab username>>;

No hints are availble for this assesment

Answer is not availble for this assesment

Previous Index Next  

Loading comments...