我有一个针对Sqlite数据库的下一个SQL查询：
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毫秒。但是，如果我放弃子查询，直接将该单个modem_id传递给查询：
SELECT * FROM messages WHERE type IN (3) AND modem_id IN( 7368859)
ORDER BY time_detected DESC LIMIT 1000
查询执行时间少于50毫秒。
我理解错了什么吗？
更新： 查询：
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毫秒，这就是问题所在。
更新：
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=?)"
更新： 在我的情况下，'modem_id IN (*)'和'type IN (*)'都可以作为标量或向量，具体取决于程序逻辑，因此解决方案是使'type IN(*)'始终作为向量，类似于'type IN(-1,*)'，在此之后所有查询都能够完美执行。