MySQL – Merging two queries that have different conditions and limits

Issue

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

In my database, I have three tables:

Posts

Primary Key: Id

Tags

Primary Key: Id | Key: Slug

TagsMap

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.

Solution

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

Answered By – Salvino

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0