MySQL - Merging two queries that have different conditions and limits

Question

I'm implementing a Tag System for my website, using PHP + MySQL.

In my database, I have three tables:

Posts

Id	Title	DateTime

Primary Key: Id

Tags

Id	Tag	Slug
1	First Tag	first-tag

Primary Key: Id | Key: Slug

TagsMap

Id	Tag

Primary Key: both

(Id = post's Id in Posts; Tag = Tag's Id in Tags)

Given, for instance, the url www. ... .net/tag/first-tag, I need to show:

• the tag's name (in this case: "First Tag");
• the last 30 published posts having that tag.

In order to achieve this, I'm using two different queries:

firstly

SELECT Tag FROM Tags WHERE Slug = ? LIMIT 1

then

SELECT p.Title FROM Posts p, Tags t, TagsMap tm
WHERE p.Id = tm.Id
AND p.DateTime <= NOW()
AND t.Id = tm.Tag
AND t.Slug = ?
ORDER BY p.Id DESC
LIMIT 30

But I don't think it's a good solution in terms of performance (please, correct me if I'm wrong).

So, my question is: how (if possible) to merge those two queries into just one?

Thanks in advance for Your suggestions.

Answer 1

If there is at most one "slug" per "post", include slug as a column in Posts.

If there can be any number of "tags" per "post", then have a table

CREATE Tags (
    post_id ... NOT NULL, 
    tag VARCHAR(..)... NOT NULL,
    post_dt DATETIME NOT NULL,
    PRIMARY KEY(post_id),
    INDEX(tag, dt)
) ENGINE=InnoDB

And you may want to use LEFT JOIN Tags and GROUP_CONCAT(tag).

I don't know what you mean by "first" in "first_tag". Maybe you should get rid of "first"?

The last 30 posts for a given tag:

SELECT p.*,
       ( SELECT GROUP_CONCAT(tag) FROM Tags ) AS tags
    FROM ( SELECT post_id  FROM tags  WHERE tag = ?
              ORDER BY post_dt DESC  LIMIT 30 ) AS x
    JOIN posts AS p ON p.id = x.post_id
 
   

Answer 2

Given that you have structured your tables in a manner where you can utilize foreign keys and match them with their counterparts, then you can make use of JOIN's in your query.

SELECT
    Tags.Tag,
    Posts.title
FROM
    Tags
LEFT JOIN
    TagsMap ON Tags.id = TagsMap.tag
LEFT JOIN
    Posts ON TagsMap.id = Posts.id AND
    Posts.DateTime <= NOW()
WHERE
    Posts.id = TagsMap.id AND
    Tags.Slug = ?
ORDER BY
    Posts.id DESC
LIMIT 30

The idea is that the query is optimized, but you will need to filter your result set programmatically in the view, in order to display the Tag only once.

Answer 3

The query that you have shown above is not a optimal solution as first it creates a cartesian product of all the tables and then filters out the data based on the conditions. If these tables become heavier in future, then your query will start slowing down (SLOW QUERIES).

Please use joins over this approach. ex. INNER JOIN, LEFT JOIN, RIGHT JOIN etc.

Try this SQL:

SELECT t.*, p.* FROM Tags t 
INNER JOIN TagsMap tm ON (tm.Tag = t.Id )
INNER JOIN Posts p ON (p.Id = tm.Id AND p.DateTime <= NOW())
WHERE t.slug LIKE 'First Tag'
ORDER BY p.Id DESC
LIMIT 30