Data Visualization 2013

< mySQL Lab - InnerJoin >
(Originally titled "sql_Innerjoin.docx" Created by Karl Yerkes, Updated by Yoon Chung Han) 

INNERJOIN

select o, i, title
  from word, title, act, count
  where
    word.bib = title.bib and word.bib = act.bib and word.bib = count.bib
  and
    word = "zapper" and
    count < 500;

the red parts of the query above constitute an implicit inner join, so even though the phrase "inner join" does not appear in the query, a 4-table inner join is executed. 

--
select o, i, title
  from word
    inner join act on act.bib = word.bib
    inner join title on title.bib = word.bib
  where word in ("red", "green", "blue", "yellow", "brown", "purple", "cyan") and o between "2010-02-05" and "2011-02-05"
  order by o desc
  limit 1000, 1000;

--
here"s how to get all the bibs that have the word ring in their title, listed in order of popularity (activity count):

select count, title
  from count
    inner join word on count.bib = word.bib
    inner join title on title.bib = word.bib
  where word = "ring"
  order by count desc limit 1000;

here"s a query for a bunch of colors:

select count, title
  from count
    inner join word on count.bib = word.bib
    inner join title on title.bib = word.bib
  where word in ("red", "green", "blue", "yellow", "brown", "purple", "cyan")
  order by count desc limit 1000;

Notice the use of a 3-way inner joins above? moving forward, there"s no way to get around it. you have to learn how to use inner join to use the SPL database effectively (i.e. fast). i can provide a few patterns like the one above, but if you want to ask new questions of the data, you"ll have to compose those questions in terms of queries that use join, so read about it.

here"s a list of tables i added and short descriptions:

* word - maps a bib to (probably) many words (all the words from it"s title)
* count - maps each bib to a count of the checkouts for that bib (aka frequency?)
* act - map a checkout (act)ivity date to a bib (a bib usually has many checkout dates)
* title - maps bib to title (each bib maps to a single title, but there may be a title that is shared by many bibs)

All of these tables are maximally indexed for speed, but you still have to compose smart queries for your app to stay fast. Use the idiom order by something limit N to get the top N results sorted on the server by relevance somehow.