Difference between revisions of "SQL Indexing"

From Suhrid.net Wiki
Jump to navigationJump to search
Line 9: Line 9:
 
* The DB combines two data structures for providing indexing - doubly linked lists and search trees.
 
* The DB combines two data structures for providing indexing - doubly linked lists and search trees.
 
* Doubly linked list enables DB to read indexes forwards and backwards. Index leaf nodes store the indexes in a DB block or page. The blocks are logically stored in the doubly linked list.  
 
* Doubly linked list enables DB to read indexes forwards and backwards. Index leaf nodes store the indexes in a DB block or page. The blocks are logically stored in the doubly linked list.  
[[File:index-leaf-nodes.jpg|frameless|200px]]
+
[[File:index-leaf-nodes.jpg|frameless|500px]]

Revision as of 05:34, 2 October 2014

These are notes that I took about SQL Indexing from http://use-the-index-luke.com

Introduction

  • An index makes a SQL query fast.
  • An index is a distinct structure in the DB that requires its own space.
  • A DB index is similar to index of a book - key concept is all entries are arranged in a well-defined order. Finding data in an ordered set is fast and easy because the sort order determines each entries position.
  • A DB index however undergoes constant change. Whenever INSERT, UPDATE, DELETE's are executed, the index must also be updated without moving around large amounts of data.
  • The DB combines two data structures for providing indexing - doubly linked lists and search trees.
  • Doubly linked list enables DB to read indexes forwards and backwards. Index leaf nodes store the indexes in a DB block or page. The blocks are logically stored in the doubly linked list.

Index-leaf-nodes.jpg