/knowsql

SQL Tips and Tricks - Presentation and Benchmarks

Primary LanguageRuby

KnowSQL

Compound Index slide was the 5 minute mark.

Goal

  1. you don't know what you don't know
  2. awareness > complete comprehension
  3. open the fire hydant: you don't have to drink it all, but I will soak you
  4. Easier

Chapter 1: Indexing

  • Basic index (bench - show seq vs idx)
  • unique index (faster than rails - bench)
  • index on expressions (w/ unique)
    • mathematical operators and functions
    • email verification regex
  • compound (multi-type indexing - Gist & GIN)
  • foreign keys
    • polymorphic foreign keys

Chapter 2: Foreign Keys

  • What is an FK in a relational db
  • dangers of invalid associations
    1. You create parent A
    2. You create child X under A
    3. Someone deletes A while you create X
    4. You go to the child page and it 500s because it can't load the parent
  • If there is no way to get to the content in your site you can orphan a lot of data
    • What if that data was videos or images?
  • Rails validations are really inefficient
    • bad: validates_presence_of :child ( has to load the child )
    • good: fk on child_id + validates_presence_of :child_id
  • Sometimes people call "Model.delete" or save(:validations => false)
  • Sometimes people load up the DB console
  • Rails validations are redundant
  • Put the validation where it matters and use rails for pretty error messages
  • Show the foreigner gem

Chapter 3: Fancy Queries

  • Joins w/ :include and :select for single query access
  • complex aggregation (partition)
  • nested queries (Subquery)
  • pattern matching (LIKE and also regex)
  • views (like for search, or for a twitter timeline)

Chapter 4: Full Text Search

Chapter 5: Geospatial

  • Setup PostGIS (w/ ubuntu repo)
  • Easy migration to add a point to something
  • Geospatial index
  • Compound index w/ geospatial
  • Searching within polygons, radii, etc
  • Fast (twitter? earthquakes?)
  • Fast (bench vs mongo geospatial? http://www.mongodb.org/display/DOCS/Geospatial+Indexing )

Chapter 6: Shoulders of Giants