title: Trouble with a join statement in SQLite
body: <p>I have two data files that I'm working with. One contains a list of words with some additional information about those words and the other one contains word pairs (where words are listed by their word IDs from the first table) and their frequencies. </p>
  
  <p>Lexicon file (sample output)</p>
  
  <pre><code>('wID', 'w1', 'w1cs', 'L1', 'c1')
  ('-----', '-----', '-----', '-----', '-----')
  (1, ',', ',', ',', 'y')
  (2, '.', '.', '.', 'y')
  (3, 'the', 'the', 'the', 'at')
  (4, 'and', 'and', 'and', 'cc')
  (5, 'of', 'of', 'of', 'io')
  </code></pre>
  
  <p>Bigram file (sample output)</p>
  
  <pre><code>('freq', 'w1', 'w2')
  (4, 22097, 161)
  (1, 98664, 1320)
  (1, 426515, 1345)
  (1, 483675, 747)
  (19, 63, 15496)
  (2, 3011, 7944)
  (1, 27985, 27778)
  </code></pre>
  
  <p>I created two tables using SQLite and uploaded the data from the files above. </p>
  
  <pre><code>conn = sqlite3.connect('bigrams.db')
  conn.text_factory = str
  c = conn.cursor()
  c.execute('pragma foreign_keys=ON')
  </code></pre>
  
  <p>Lexicon table</p>
  
  <pre><code>c.execute('''CREATE TABLE lex
              (wID INT PRIMARY KEY, w1 TEXT, w1cs TEXT, L1 TEXT, c1 TEXT)''')
  
  #I removed this index as per CL.'s suggestion
  #c.execute('''DROP INDEX IF EXISTS lex_index''') 
  #c.execute('''CREATE INDEX lex_index ON lex (wID, w1, c1)''')
  
  #and added this one
  c.execute('''CREATE INDEX lex_w1_index ON lex (w1)''')
  </code></pre>
  
  <p>Insert data into the lexicon table</p>
  
  <pre><code>#I replaced this code
  # with open('/Users/.../lexicon.txt', "rb") as lex_file:
  #    for line in lex_file:
  #        currentRow = line.split('\t')
  #        try:
  #            data = [currentRow[0], currentRow[1], currentRow[2], currentRow[3], str(currentRow[4].strip('\r\n'))]
  #           c.executemany ('insert or replace into lex values (?, ?, ?, ?, ?)', (data,))
  #        except IndexError:
  #            pass   
  
  
  #with the one that Julian wrote
  
  blocksize = 100000
  
  with open('/Users/.../lexicon.txt', "rb") as lex_file:
      data = []
      line_counter = 0
      for line in lex_file:
          data.append(line.strip().split('\t'))
          line_counter += 1
          if line_counter % blocksize == 0:
              try:
                  c.executemany ('insert or replace into lex values (?, ?, ?, ?, ?)', data)
                  conn.commit()
              except IndexError:
                  block_start = line_counter - blocksize + 1
                  print 'Lex error lines {}-{}'.format(block_start, line_counter)
              finally:
                  data = []
  </code></pre>
  
  <p>Bigram table</p>
  
  <pre><code>#I replaced this code to create table x2 
  #c.execute('''CREATE TABLE x2
  #             (freq INT, w1 INT, w2 INT, FOREIGN KEY(w1) REFERENCES lex(wID), FOREIGN KEY(w2) REFERENCES lex(wID))''')
  
  #with the code that Julian suggested
  c.execute('''CREATE TABLE x2
               (freq INT, w1 INT, w2 INT,
                FOREIGN KEY(w1) REFERENCES lex(wID),
                FOREIGN KEY(w2) REFERENCES lex(wID),
                PRIMARY KEY(w1, w2) )''')
  </code></pre>
  
  <p>Insert data into the bigram table </p>
  
  <pre><code>#Replaced this code
  #with open('/Users/.../x2.txt', "rb") as x2_file:
  #    for line in x2_file:
  #        currentRow = line.split('\t')
  #        try:
  #            data = [str(currentRow[0].replace('\x00','').replace('\xff\xfe','')), str(currentRow[1].replace('\x00','')), str(currentRow[2].replace('\x00','').strip('\r\n'))]
  #           c.executemany('insert or replace into x2 values (?, ?, ?)', (data,))
  #        except IndexError:
  #            pass
  
  #with this one suggested by Julian 
  with open('/Users/.../x2.txt', "rb") as x2_file:
      data = []
      line_counter = 0
      for line in x2_file:
          data.append(line.strip().replace('\x00','').replace('\xff\xfe','').split('\t'))
          line_counter += 1
          if line_counter % blocksize == 0:
              try:
                  c.executemany('insert or replace into x2 values (?, ?, ?)', data)
                  conn.commit()
              except IndexError:
                  block_start = line_counter - blocksize + 1
                  print 'x2 error lines {}-{}'.format(block_start, line_counter)
              finally:
                  data = []
  
  conn.close()
  </code></pre>
  
  <hr>
  
  <p>I want to be able to check if a given word pair exists in the data -- for example "like new"</p>
  
  <p>When I specify the first word only, the program works just fine. </p>
  
  <pre><code>cur.execute('''SELECT lex1.w1, lex2.w1 from x2 
                  INNER JOIN lex as lex1 ON lex1.wID=x2.w1
                  INNER JOIN lex as lex2 ON lex2.wID=x2.w2
                  WHERE lex1.w1= “like” ’’’)
  </code></pre>
  
  <p>But when I want to search for a pair of words, the code is painfully slow. </p>
  
  <pre><code>cur.execute('''SELECT lex1.w1, lex2.w1 from x2 
                      INNER JOIN lex as lex1 ON lex1.wID=x2.w1
                      INNER JOIN lex as lex2 ON lex2.wID=x2.w2
                      WHERE lex1.w1=“like” AND lex2.w1= “new” ''')
  </code></pre>
  
  <p>I can't figure out what I'm doing wrong. 
  Any help would be much appreciated. </p>