我有下一个SQL查询到Sqlite数据库：

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

其中子查询带来单个数据行。查询在我的数据上执行大约7秒。单独的子查询执行时间少于1 ms。但是如果我去掉子query并将这个modem_id直接传递给query：

SELECT * FROM messages   WHERE type IN (3) AND modem_id IN( 7368859) 
ORDER BY time_detected DESC LIMIT 1000

查询执行少于50 ms。
我被误解了什么？
UPD：查询：

SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN( SELECT 7368859) ORDER BY time_detected DESC LIMIT 1000

执行7秒。和查询

SELECT * FROM  messages   WHERE  type IN (3) AND modem_id IN(7368859) ORDER BY time_detected DESC LIMIT 1000

执行44 ms。这就是问题所在。
UPD：

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;

查询计划：

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=?)"

UPD：在我的例子中，'modem_id IN（）'和'type IN（）'都可以作为标量作为向量，并取决于程序逻辑，所以解决方案是使'type IN（）'始终作为向量，一些类似'type IN（-1，）'的东西在此之后所有查询都完美执行。