title: WebSQL query optimization help - CASE WHEN makes query 400 ms slow
body: <p>Can somebody tell me why 'CASE WHEN' makes it so slow and how to optimize / fix it, please ?</p>
  
  <p>It is needed to get the items that are pinned to be put first in the result and in order.</p>
  
  <p>I could probably do it after the sql query but i think it would be faster, when done right, if this sorting is done inside the sql query.</p>
  
  <p>slow query ~490ms</p>
  
  <pre><code>SELECT 
      places.id AS place_id,
      url,
      title,
      thumbnails.score AS score,
      thumbnails.clipping AS clipping,
      thumbnails.lastModified AS lastModified,
      EXISTS (SELECT 1 FROM pinned pi WHERE pi.place_id = places.id) AS pinned
  FROM places
      LEFT JOIN thumbnails ON (thumbnails.place_id = places.id)
      LEFT JOIN pinned j ON (j.place_id = places.id) WHERE (hidden == 0)
  ORDER BY case when j.id is null then 1 else 0 end,
      j.id,
      frecency DESC LIMIT 24
  </code></pre>
  
  <p>Removing the 'CASE WHEN' part:
  query ~6ms</p>
  
  <pre><code>SELECT
      places.id AS place_id,
      url,
      title,
      thumbnails.score AS score,
      thumbnails.clipping AS clipping,
      thumbnails.lastModified AS lastModified,
      EXISTS (SELECT 1 FROM pinned pi WHERE pi.place_id = places.id) AS pinned
  FROM places
      LEFT JOIN thumbnails ON (thumbnails.place_id = places.id) WHERE (hidden == 0)
  ORDER BY frecency DESC LIMIT 24
  </code></pre>
  
  <p>Table info:</p>
  
  <pre><code>var Create_Table_Places =
      'CREATE TABLE places (' +
          'id INTEGER PRIMARY KEY,' +
          'url LONGVARCHAR,' +
          'title LONGVARCHAR,' +
          'visit_count INTEGER DEFAULT 0,' +
          'hidden INTEGER DEFAULT 0 NOT NULL,' +
          'typed INTEGER DEFAULT 0 NOT NULL,' +
          'frecency INTEGER DEFAULT -1 NOT NULL,' +
          'last_visit_date INTEGER,' +
          'dateAdded INTEGER,' +
          'lastModified INTEGER' +
      ')';
  
  var Create_Table_Thumbnails =
      'CREATE TABLE thumbnails (' +
          'id INTEGER PRIMARY KEY,' +
          'place_id INTEGER UNIQUE,' +
          'data LONGVARCHAR,' +
          'score REAL,' +
          'clipping INTEGER,' +
          'dateAdded INTEGER,' +
          'lastModified INTEGER' +
      ')';
  
  var Create_Table_Pinned =
      'CREATE TABLE pinned (' +
          'id INTEGER PRIMARY KEY,' +
          'place_id INTEGER UNIQUE,' +
          'position INTEGER,' +
          'dateAdded INTEGER,' +
          'lastModified INTEGER' +
      ')';
  </code></pre>