使用slug而不是ID获取记录 [英] Fetching records with slug instead of ID

问题描述

我目前正在尝试找到最好的方式（在可用性和性能方面），当处理像抓取用特定标签或类别等标记的记录的情况。

I'm currently trying to find the best way (in term of usability and performance) when dealing with a situation like fetching records tagged with a specific tag, or category, or something like that.

一个很好的方法（我想要的方式）是使用标签/类别slug获取记录，因此URL将如下所示：

A good way (the way I wanted to do), would be to fetch records with the tag/category slug, so the URL would look like :

http://stackoverflow.com/questions/tagged/language-agnostic

通过slug获取记录，看起来比：

fetching records by slug, which looks better than :

http://stackoverflow.com/questions/tag/789/language-agnostic

通过ID获取并添加slug，发动机友好。这是更好的性能，因为通过整数ID获取数据将比字符串更快。 （cmiiw）

fetching by ID and adding the slug behind so it's more search-engine friendly. This one is better performance-wise, because fetching data by an integer ID would be faster than a string. (cmiiw)

现在，使用数据库模式：

Now, with a db schema like :

posts    post_to_tags    tags
-----    ------------    ----
id       id              id
title    post_id         name
content  tag_id          slug
...                      ...

我做对吗？有没有陷阱或最佳实践，我需要知道，以避免性能问题？ （例如，标签不应超过10,000条记录，或标签段不应超过n个字符或其他内容）

am I doing it right ? Is there pitfall or best-practices that I need to know to avoid performance problems ? (eg. tags should not exceed 10,000 records, or tag slug should not exceed n characters, or something else)

提前感谢。

推荐答案

使用第一个网址样式和当前的数据库设计，您可以：

With the first URL style and your current db design, you can do this:

select ...
from   posts p
join   posts_to_tags pt on pt.post_id = p.post_id
join   tags t on t.id = pt.tag_id
where  t.slug = [url slug value];

只要tags.slug被索引，这应该是非常有效的， em>任何不同于

As long as tags.slug is indexed, this should be very efficient, hardly any different from

select ...
from   posts p
join   posts_to_tags pt on pt.post_id = p.post_id
where  pt.tag_id = [url tag ID];

这篇关于使用slug而不是ID获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！