Indexes Jennifer Widom Indexes Primary mechanism to get

Indexes § Primary mechanism to get improved performance on a database § Persistent data

Functionality Indexes T Index on T. A A B C 1 cat 2 …

Functionality Indexes T Index on T. A Index on T. B A B C

Functionality Indexon T. A on T. (A, B) Index on T. B Indexes T

Utility Indexes § Index = difference between full table scans and immediate location of

Indexes Select s. Name From Student Where s. ID = 18942 Many DBMS’s build

Indexes Select s. ID From Student Where s. Name = ‘Mary’ And GPA >

Indexes Select s. Name, c. Name From Student, Apply Where Student. s. ID =

Downsides of Indexes 1) 2) 3) Jennifer Widom

Picking which indexes to create Indexes Benefit of an index depends on: § Size

“Physical design advisors” Input: database (statistics) and workload Output: recommended indexes Database statistics Query

SQL Syntax Indexes Create Index. Name on T(A) Create Index. Name on T(A 1,

Slides: 14

Download presentation

Indexes Jennifer Widom

Indexes § Primary mechanism to get improved performance on a database § Persistent data structure, stored in database § Many interesting implementation issues But we are focusing on user/application perspective Jennifer Widom

Functionality Indexes T Index on T. A A B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … … Jennifer Widom

Functionality Indexes T Index on T. A Index on T. B A B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … … Jennifer Widom

Functionality Indexon T. A on T. (A, B) Index on T. B Indexes T A B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … … Jennifer Widom

Utility Indexes § Index = difference between full table scans and immediate location of tuples âOrders of magnitude performance difference § Underlying data structures – Balanced trees (B trees, B+ trees) – Hash tables Jennifer Widom

Indexes Select s. Name From Student Where s. ID = 18942 Many DBMS’s build indexes automatically on PRIMARY KEY (and sometimes UNIQUE) attributes Jennifer Widom

Indexes Select s. ID From Student Where s. Name = ‘Mary’ And GPA > 3. 9 Jennifer Widom

Indexes Select s. Name, c. Name From Student, Apply Where Student. s. ID = Apply. s. ID Jennifer Widom

Downsides of Indexes 1) 2) 3) Jennifer Widom

Picking which indexes to create Indexes Benefit of an index depends on: § Size of table (and possibly layout) § Data distributions § Query vs. update load Jennifer Widom

“Physical design advisors” Input: database (statistics) and workload Output: recommended indexes Database statistics Query or update Indexes Query Optimizer Best execution plan with estimated cost Jennifer Widom

SQL Syntax Indexes Create Index. Name on T(A) Create Index. Name on T(A 1, A 2, …, An) Create Unique Index. Name on T(A) Drop Index. Name Jennifer Widom