时间戳

首页 > 解决方案 > 如何从视图的连接和后续查询中索引视图？

sql - 如何从视图的连接和后续查询中索引视图？

问题描述

我是 sql 索引的新手，想索引这个

CREATE TABLE IF NOT EXISTS message (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    remoteId TEXT,
    text TEXT,
    timestamp INTEGER NOT NULL DEFAULT 0,
    channelId TEXT NOT NULL,
    accountId TEXT NOT NULL,

    username TEXT NOT NULL,
    editUsername TEXT,
    wm_createdUsername TEXT,
    wm_organizedUsername TEXT,
    rc_username TEXT,
    p_messageRemoteId TEXT,
    c_messageRemoteId TEXT,
    ....

    UNIQUE (remoteId, accountId),
    FOREIGN KEY (channelId, accountId) REFERENCES channel(id, accountId) ON DELETE CASCADE,
    FOREIGN KEY (p_messageRemoteId, accountId) REFERENCES message(remoteId, accountId) ON DELETE CASCADE,
    FOREIGN KEY (c_messageRemoteId, accountId) REFERENCES message(remoteId, accountId) ON DELETE CASCADE
);

CREATE VIEW messageAndUser AS
SELECT ...
FROM message
LEFT JOIN user AS u ON message.username = u.name AND message.accountId = u.accountId
LEFT JOIN user AS e ON message.editUsername = e.name AND message.accountId = e.accountId
LEFT JOIN user AS wm ON message.wm_organizedUsername = wm.name AND message.accountId = wm.accountId
LEFT JOIN user AS rc ON message.rc_username = rc.name AND message.accountId = rc.accountId
LEFT JOIN user AS i ON message.i_username = i.name AND message.accountId = i.accountId;

频道消息：

SELECT *
FROM messageAndUser
WHERE channelId=? AND accountId=?
ORDER BY timestamp DESC

;

我的直觉告诉我，索引需要作为一个单元放在 AND 语句的所有部分上。

但是，我不确定该怎么做是，如果我有一个涵盖视图中连接的所有部分的索引，现在如果来自该视图的查询，即select from view where foo=?，我是否需要创建包含索引中所有列的新索引对于视图 + foo？或者有没有办法组成这个？

标签： sqlsqliteindexingviewsql-view

解决方案

视图不能直接索引，但可以从视图查询中涉及的表上的索引中受益。

查看您的架构和视图似乎您错过了连接的主索引 

table  message  column   accountId

做到唯一索引 UNIQUE (remoteId, accountId 在右侧显示 accountId .. 这对于视图中使用的连接没有用..

推荐阅读