title: SQLite query with subquery performance
body: <p>I have a next SQL  query to Sqlite database:</p>
  
  <pre><code>SELECT * FROM messages   WHERE type IN (3) AND modem_id IN( 
      SELECT device_id FROM client_devices WHERE client_id=0 AND device_id IN (7368859)) 
  ORDER BY time_detected DESC LIMIT 1000
  </code></pre>
  
  <p>Where the subquery brings the single data row. The query executes on  my data about 7sec. The separate subquery executes less then 1ms.  But if I get rid subquery and pass this single modem_id direct to  query :</p>
  
  <pre><code>SELECT * FROM messages   WHERE type IN (3) AND modem_id IN( 7368859) 
  ORDER BY time_detected DESC LIMIT 1000
  </code></pre>
  
  <p>the query executes less then 50ms. </p>
  
  <p>What I am misunderstood?</p>
  
  <p>UPD:
  The query :</p>
  
  <pre><code>SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN( SELECT 7368859) ORDER BY time_detected DESC LIMIT 1000
  </code></pre>
  
  <p>executes 7sec. And the query </p>
  
  <pre><code>SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN(7368859) ORDER BY time_detected DESC LIMIT 1000
  </code></pre>
  
  <p>Executes 44ms. 
  That is the problem.</p>
  
  <p>UPD:</p>
  
  <pre><code>BEGIN TRANSACTION;
  CREATE TABLE IF NOT EXISTS `stations` (
      `bs_id` INTEGER NOT NULL UNIQUE,
      `online_status` INTEGER,
      `dl_status` INTEGER,
      `status_duration`   INTEGER,
      `noise` INTEGER,
      `temperature`   INTEGER,
      `dl_busyness`   INTEGER,
      `dl_aver_busyness`  INTEGER,
      `bs_state`  INTEGER,
      `rev_list`  TEXT,
      `ul_bitrates`   TEXT,
      `dl_bitrates`   TEXT,
      `ul_base_freqs` TEXT,
      `dl_base_freqs` TEXT,
      `last_hb_time`  INTEGER,
      `bs_type`   TEXT,
      `timezone_offset`   INTEGER NOT NULL DEFAULT (10800),
      PRIMARY KEY(`bs_id`)
  );
  CREATE TABLE IF NOT EXISTS `radiomodems` (
      `id`    INTEGER,
      `batch_id`  INTEGER,
      `nbfi_ver`  INTEGER NOT NULL DEFAULT 0,
      `hw_type`   TEXT,
      `protocol`  TEXT,
      `dl_strength`   INTEGER NOT NULL DEFAULT 26,
      `ul_messages_per_ack`   INTEGER NOT NULL DEFAULT 1,
      `dl_messages_per_ack`   INTEGER NOT NULL DEFAULT 1,
      `ul_base_freq`  INTEGER NOT NULL DEFAULT 868800000,
      `dl_base_freq`  INTEGER DEFAULT 446000000,
      `dl_mode`   INTEGER NOT NULL DEFAULT 0,
      `dl_phy`    TEXT NOT NULL DEFAULT 'DL_PSK_200',
      `dl_num_of_retries` INTEGER NOT NULL DEFAULT 3,
      `key`   TEXT,
      `bs_data`   TEXT,
      `ul_bitrates`   TEXT,
      `dl_bitrates`   TEXT,
      PRIMARY KEY(`id`)
  );
  CREATE TABLE IF NOT EXISTS `messages` (
      `id`    INTEGER PRIMARY KEY AUTOINCREMENT,
      `modem_id`  INTEGER NOT NULL,
      `station_id`    INTEGER NOT NULL,
      `time_detected` INTEGER NOT NULL,
      `time_saved`    INTEGER NOT NULL,
      `type`  INTEGER NOT NULL DEFAULT (0),
      `iterator`  INTEGER NOT NULL,
      `payload`   BLOB NOT NULL,
      `snr`   INTEGER NOT NULL,
      `rssi`  INTEGER NOT NULL,
      `freq`  INTEGER NOT NULL,
      `phy`   INTEGER NOT NULL,
      `comment`   TEXT
  );
  CREATE TABLE IF NOT EXISTS `downlinks` (
      `tag_id`    TEXT,
      `modem_id`  INTEGER NOT NULL,
      `station_id`    INTEGER NOT NULL DEFAULT (0),
      `payload`   BLOB NOT NULL,
      `flags` INTEGER NOT NULL DEFAULT (0),
      `status`    INTEGER NOT NULL,
      `posted_time`   INTEGER NOT NULL DEFAULT (strftime('%s','now','utc')),
      `placeholder`   TEXT,
      PRIMARY KEY(`tag_id`)
  );
  CREATE TABLE IF NOT EXISTS `clients` (
      `id`    INTEGER,
      `apikey`    TEXT NOT NULL UNIQUE,
      `role`  INTEGER NUT DEFAULT 1,
      PRIMARY KEY(`id`)
  );
  CREATE TABLE IF NOT EXISTS `client_devices` (
      `client_id` INTEGER NOT NULL,
      `device_id` INTEGER NOT NULL,
      FOREIGN KEY(`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
      PRIMARY KEY(`client_id`,`device_id`),
      FOREIGN KEY(`device_id`) REFERENCES `radiomodems`(`id`) ON DELETE CASCADE
  );
  CREATE INDEX IF NOT EXISTS `time4_idx` ON `messages` (
      `type`,
      `time_detected`
  );
  CREATE INDEX IF NOT EXISTS `time3_idx` ON `messages` (
      `type`,
      `modem_id`,
      `time_detected`
  );
  CREATE INDEX IF NOT EXISTS `time2_idx` ON `messages` (
      `type`,
      `station_id`,
      `time_detected`
  );
  CREATE INDEX IF NOT EXISTS `time1_idx` ON `messages` (
      `type`,
      `modem_id`,
      `station_id`,
      `time_detected`
  );
  CREATE INDEX IF NOT EXISTS `modem_id_idx` ON `radiomodems` (
      `id`
  );
  CREATE INDEX IF NOT EXISTS `dl_tag_id_idx` ON `downlinks` (
      `tag_id`
  );
  CREATE INDEX IF NOT EXISTS `dl_status_idx` ON `downlinks` (
      `status`
  );
  CREATE INDEX IF NOT EXISTS `client_dev_idx` ON `client_devices` (
      `device_id`
  );
  CREATE INDEX IF NOT EXISTS `batch_idx` ON `radiomodems` (
      `batch_id`
  );
  CREATE INDEX IF NOT EXISTS `apikey_idx` ON `clients` (
      `apikey`
  );
  COMMIT;
  </code></pre>
  
  <p>Query plans:</p>
  
  <pre><code>explain query plan SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN( SELECT 7368859) ORDER BY time_detected DESC LIMIT 1000
  "0" "0" "0" "SEARCH TABLE messages USING INDEX time4_idx (type=?)"
  "0" "0" "0" "EXECUTE LIST SUBQUERY 1"
  
  explain query plan SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN(7368859) ORDER BY time_detected DESC LIMIT 1000
  "0" "0" "0" "SEARCH TABLE messages USING INDEX time3_idx (type=? AND modem_id=?)"
  </code></pre>
  
  <p>UPD:
  In my case 'modem_id IN ( * )' and 'type IN ( * )' both can be as scalars as vectors and depends on program logic, so  solution was makes 'type IN( * )' always  as vector,  some thing like 'type IN(-1,* )' after this all queries executes perfect.</p>