I’m implementing a Tag System for my website, using PHP + MySQL.
In my database, I have three tables:
Primary Key: Id
Primary Key: Id | Key: Slug
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:
SELECT Tag FROM Tags WHERE Slug = ? LIMIT 1
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.
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 (
Please use joins over this approach. ex.
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