Indexes

• This is from Widom and Ullman
• A binary tree is a data structure
  • One node constitutes the root.
  • A node can have 0, 1 or two children
  • Nodes have associated values
  • A binary search tree is a binary tree with the binary search tree property.
    • The left subtree contains values less than or equal to values in the parent.
    • The right subtree contains values greater than the value at the root.
    • This rule applies recursively
• Binary search trees can, in theory be O(log₂n) high.
  • Which means that they can be searched in O(log₂ n) time.
  • Quick calculation of log₂(n)
  • But they can be as bad as O(n)
• A B-tree or balanced binary tree
  • By spending more time inserting and deleting we can spend less time searching.
  • The building of a B-tree is beyond the scope of this class.
  • But there are several, AVL, Red-Black
• An index of an attribute on a relation allows for efficient searching on that attribute.
• The attribute needs a fixed value.
• SELECT * FROM SKU_DATA WHERE Department = "WaterSports" AND Buyer LIKE "% Smith"
  • This will probably examine every tuple in the table.
  • Not bad for our table of 10 or so values, but how about one with 100,000 values?
  • Notice I did not query on SKU, as this is the primary key
  • If we had a second tree, which had things sorted by Department, this might be helpful.
• This could be even more important on a join
  • SELECT * from T1, T2 WHERE T1.A = x and T2.B = y
  • Instead of crossing T1 &tiems; T2,
  • We could form T1.A × T2
  • or we could cross T1.A = x × T2.B = y
• Creating Indexes
  • MySQL reference
  • Don't use it on a primary key, this is already an index.
  • UNIQUE
    • Unique values in columns selected are required
    • Multiple nulls are permitted.
  • FULL TEXT
    • Only in MyISAM tables
    • Indexing happens across the entire column
  • Index Type: BTREE, HASH
    • BTREE is available for INNODB
  • CREATE INDEX DepartmentIndex on SKU_DATA (Department)
  • But we can create an index on multiple attributes as well.
  • Indexes can also be created when the table is created.
    • The KEY attribute is synonymous for INDEX
    • PRIMARY KEY is a unique index.
  • SHOW INDEX IN table
• Widom states that if compound index keys are used, the final value is usually a concatenation of the individual components,
  • So a search based on the first component using that index will be fast
  • So the order of items in an index is important.
• Indexes:
  • Speed up searches and joins
  • But slow down insert/deletes and updates on the index keys
• From See this page