英文:
Using WHERE and AND clauses with index in SQL
问题
我有两个表
创建表 profile (
id BIGSERIAL 主键,
昵称 VARCHAR,
注册时间 TIMESTAMP
);
创建表 post (
id BIGSERIAL 主键,
拥有者_id BIGINT 引用自 profile(id),
正文 TEXT,
插入时间 TIMESTAMP,
点赞数 INT
);
一个索引
在 post 上创建索引 (owner_id);
和一个查询
选择 * 从 post 其中 likes_count = ? 并且 owner_id = ?
查询中会使用这个索引吗？
英文:
I have two tables
CREATE TABLE profile (
id BIGSERIAL PRIMARY KEY,
nickname VARCHAR,
registered_at TIMESTAMP
);
CREATE TABLE post (
id BIGSERIAL PRIMARY KEY,
owner_id BIGINT REFERENCES profile(id),
body TEXT,
inserted_at TIMESTAMP,
likes_count INT
);
an index
CREATE INDEX ON post (owner_id);
and a query
SELECT * FROM post WHERE likes_count = ? AND owner_id = ?
Will the index be used in the query?
答案1
得分: 3
假设这是MSSQL（看起来是的），那么是的。
一般来说，大多数数据库引擎应该利用owner_id上的索引来限制在扫描likes_count条件之前返回的行数，但这并不是一个保证，因为它高度依赖于RDS的实现方式。
话虽如此，对于这类问题，你最好的选择是查看你正在使用的任何RDS平台上的执行计划，这将直接为你的工作平台回答你的问题，具体而明确。
英文:
Assuming this is MSSQL (looks like it), then yes.
Generally speaking, most database engines should utilize the index on owner_id to limit the rows returned for the select prior to scanning for the likes_count criteria, but that's not a guarantee as it is highly dependent on the RDS implementation.
With that being said, your best bet for questions like these is to view the execution plan on whatever RDS platform you're using--that will answer your question directly and specifically for the platform you're working with.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论