帖子和标签 - 加入限制

发布于 2024-12-29 05:56:34 字数 480 浏览 0 评论 0原文

我有 3 个表： posts、tags、*posts_tags* 。我想列出帖子以及与其关联的所有标签，但要限制结果。

这就是我现在所做的：

SELECT 
    p.*,
    t.*
FROM 
    (
        SELECT * FROM  posts LIMIT 0, 10
    ) as p
    LEFT JOIN
        posts_tags as pt
        ON pt.post_id = p.post_id
    LEFT JOIN
        tags as t
        ON t.tag_id = pt.tag_id

它工作正常，但似乎有点慢..

有更好/更快的方法吗？我可以在其他地方应用 LIMIT 以获得更好的结果吗？

编辑：我想限制帖子，而不是结果。一个帖子可以有多个标签。

原文

I have 3 tables: posts, tags, *posts_tags* . I want to list posts, and all tags associated with them, but to limit the results.

This is what I do now:

SELECT 
    p.*,
    t.*
FROM 
    (
        SELECT * FROM  posts LIMIT 0, 10
    ) as p
    LEFT JOIN
        posts_tags as pt
        ON pt.post_id = p.post_id
    LEFT JOIN
        tags as t
        ON t.tag_id = pt.tag_id

It is working fine, but it seems to be a little bit slow..

Is there a better/faster way of doing this? Can I apply LIMIT somewhere else for better results?

EDIT: I want to limit posts, and not results. A post can have many tags.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江挽川 2025-01-05 05:56:34

您是否尝试过将限制子查询移至 where 子句：

SELECT 
    p.*,
    t.*
FROM 
    posts as p
LEFT JOIN
    posts_tags as pt
    ON pt.post_id = p.post_id
LEFT JOIN
    tags as t
    ON t.tag_id = pt.tag_id
WHERE
    p.post_id in (select post_id from post limit 0,10)

Have you tried moving the limiting subquery to the where clause instead:

SELECT 
    p.*,
    t.*
FROM 
    posts as p
LEFT JOIN
    posts_tags as pt
    ON pt.post_id = p.post_id
LEFT JOIN
    tags as t
    ON t.tag_id = pt.tag_id
WHERE
    p.post_id in (select post_id from post limit 0,10)

回复收藏 0 原文

一刻暧昧 2025-01-05 05:56:34

尝试在前面使用 EXPLAIN 关键字运行查询：

EXPLAIN SELECT ...

这将使您了解 MySQL 如何执行查询。也许您在某处错过了某个键或索引。以下是如何读取 EXPLAIN 的结果：
http://dev.mysql.com/doc/refman/5.5 /en/explain-output.html

Try running your query with the EXPLAIN keyword in front of it:

EXPLAIN SELECT ...

This will give you and idea about how MySQL is executing your query. Maybe you miss a key or an index somewhere. Here's how to read the result of EXPLAIN:
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

回复收藏 0 原文

夜血缘 2025-01-05 05:56:34

SELECT 
    p.*,
    t.*
FROM 
    posts as p
    LEFT JOIN
        posts_tags as pt
        ON pt.post_id = p.post_id
    LEFT JOIN
        tags as t
        ON t.tag_id = pt.tag_id
LIMIT 0, 10

应该可以工作；）

编辑

MySQL在运行多个联接时非常慢，在我看来，最好将查询分成两个，然后将结果联接到应用程序代码中（应用程序开销不应该太大，因为它只有 10 个结果）。

SELECT 
    p.*,
    t.*
FROM 
    posts as p
    LEFT JOIN
        posts_tags as pt
        ON pt.post_id = p.post_id
    LEFT JOIN
        tags as t
        ON t.tag_id = pt.tag_id
LIMIT 0, 10

Should work ;)

EDIT

MySQL is quite slow when running multiple joins, in my opinion it's better to separate your query into two and then join the result in your app code (application overhead should not be so big since its only 10 results).

回复收藏 0 原文

~没有更多了~