Database Systems with SQL - Full Course - Database Systems - Single Level Indexes - Primary Secondary Clustering Dense and Sparse Index

Indexing is a data structure technique which allows you to quickly retrieve records from a database file A single-level index is a file containing values for a single column (typically a primary key), which point to a row containing the row that match up to the column value. If an indexed column is unique, the index has one entry for each column value. However, if the indexed column is not unique, the index could have multiple entries for some column values, or one entry for each column value, followed by multiple pointers. A single-level index is usually defined on a single column, but in a multi-column index, each index is a composite of multiple indexed column values. The CREATE INDEX command creates an index on a table: CREATE INDEX index_nameON table_name (column1, column2, ...); For example: CREATE INDEX IndexLastNameON Person (LastName); In the example diagram, there is an index on the State column of the City table. Each column value is sorted in the index, and has a pointer to the row where the states match up. If the columns are not unique, then they are a new entry in the index. Each index can have multiple pointers for the same value, as in the CA example which points to San Diego and Compton. When the database is executing a SELECT query, it can perform a table scan or an index scan A table scan reads table blocks directly, without accessing an index. An index scan reads index blocks in order to find the table blocks its looking for. The % of table rows selected by a query is called the hit ratio. When a SELECT query is executed, the database examines the WHERE clause does a table scan if the hit ratio is high, or looks for an indexed column with an index scan, if the hit ratio is low it scans the index. Note that if the WHERE clause doesn’t include column that is indexed, then a table scan is performed. Index scans are about 10x faster than table scans, and that is why indexes are useful if you wan an optimized database. There are also some cases where a binary search may be faster than an index scan. Binary search requires that a column be sorted, and it keeps splitting the rows in half, until it quickly finds the results it’s looking for. Similarly to a phone book, when you are looking for Dominos pizza, and you open the book in the middle and you’re at letter M for McDonalds, you can eliminate the 2nd half of the book and repeat the process for the first half. Primary Index is an index on a unique sort column. Secondary index is an index that is not on the sort column. Clustering index is an index on a non-unique sort column. These indexes can be one of two types: Dense Index - contains an entry for each table row. Sparse Index - contains an entry for each table block. Inserts, updates, and deletes to tables have an impact on single-level indexes. Insert – Since a new row is added to a table, a new index needs to be created and placed in the right location. This involves moving things around, which could be time-consuming or slow for large tables. To help speed this up, the database just splits the index block and reallocates entries to the new block after it created some space for the new entry. Delete. When a row is deleted, the row's index entry needs to also be deleted. This also causes the issue of having to move around surrounding entries if one of them gets deleted, so to help speed this process up, the entries are simply marked as ‘deleted’. The database could later remove all entries marked for deletion and compress the index. Update. An index is not affected if there is an update to a column that is not indexed. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!