title: Beginner to sqlite trying to improve performance through database design, 'join' and 'where'
body: <p>I have a large table (potentially millions of rows) that in its naive form would contain lots of repetition, e.g.:</p>
  
  <pre><code>CREATE TABLE sales(
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      salesperson TEXT,
      customer    TEXT
  );
  
  INSERT INTO sales VALUES(NULL, "Rod", "Acme");
  INSERT INTO sales VALUES(NULL, "Jane", "Xyz Corp");
  INSERT INTO sales VALUES(NULL, "Freddy", "Acme");
  &lt;... many more lines containing significant repetitions of each salesperson and each customer &gt;
  </code></pre>
  
  <p>I think I know enough that this is not the ideal approach when I want to perform queries such as:</p>
  
  <pre><code>SELECT count(*) FROM sales WHERE salesperson="Jane";
  </code></pre>
  
  <p>So my database should be rearranged as:</p>
  
  <pre><code>CREATE TABLE sales(
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      salesperson INTEGER,
      customer    INTEGER
  );
  
  CREATE TABLE salespeople(
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT
  );
  
  CREATE TABLE customers(
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT
  );
  </code></pre>
  
  <p>In reality there are many more columns in the single-table solution, and therefore more distinct tables in the normalized (if that's even the right use of the term) version.</p>
  
  <p>I've timed tests of a dumb query against the single-table v.s. a JOIN with a WHERE against the split-tables version and the "smart" version is about 25% slower, so clearly I'm missing something:</p>
  
  <pre><code>SELECT count(*) FROM sales INNER JOIN salespeople ON sales.salesperson=salespeople.id WHERE salespeople.name="Freddy";
  </code></pre>
  
  <p>If I lookup the salesperson.id first as a separate query, I see times ~33% faster than the dumb implementation (which is less than I was hoping for).</p>
  
  <pre><code>SELECT id FROM salespeople WHERE name="Freddy";
  </code></pre>
  
  <p>...then</p>
  
  <pre><code>SELECT count(*) FROM sales WHERE salesperson=previouslyLookedUpId;
  </code></pre>
  
  <p>I get the impression that the WHERE (in the multi-table solution) is being evaluated for every row, rather than once to determine the relevant salesperson id, as intended. Clearly my queries (or constraints, or indices?) aren't allowing the db engine to work efficiently.</p>
  
  <p>I only know enough to know that I don't know enough... What is the right approach here?</p>