MySQL Need help defining SQL to remove unwanted rows

Question

So the project is making a simple CMS in PHP. You have posts, categories and tags. It will be handling millions of posts in one database, millions more tags and categories.

The problem: Optimally, you want to be able to select 30 posts that must be under categories 5 and tags 1 and 2. You want it done in as little queries as possible...

termRelations contains the post ID and term ID, with termTypeId distinguishing between the cats and tags tables.
cats contains the term ID and category info (Name, slug etc.)
tags contains the term ID and tag info (Name, slug etc.)

cats and tags are seperate tables so to speed up generating a category list/ to define them more seperately.

SELECT DISTINCT *
FROM posts
LEFT JOIN termRelations ON ( posts.id = termRelations.postId )
LEFT JOIN cats ON ( termRelations.termId = cats.id AND termRelations.termTypeId = 1 )
LEFT JOIN tags ON ( termRelations.termId = tags.id AND termRelations.termTypeId = 0 )
WHERE cats.id =5
OR tags.id =2
OR tags.id =1
LIMIT 0 , 30

In this case it returns 3 rows for one post, the first two with tag fields added, the last with the category fields.

I do not need this information for use (As that seems impossible when it comes to multiple tags or categories in one row. Maybe not?), I merely need to grab posts under those three terms. Though, if I could get category and tag info with one query that would be optimal.

Thanks. This is screwing with my brain. If I am doing something wrong and you know a more efficient way to do this then I would be happy to re structure the database.

Answer 1

DISTINCT works on all columns in the SELECT so has you are SELECTing everything it will return each distinct row and not just the distinct posts. To get round this you could just SELECT the data from the posts table and then DISTINCT it, i.e.

SELECT DISTINCT posts.*

But you've also said you would like the posts and cats info as well if possible. One way to do this and keep one row per post is to use GROUP_CONCAT so your query might end up something like this.

SELECT 
   posts.*,
   GROUP_CONCAT(cats.id SEPARATOR ',') as catsList,
   GROUP_CONCAT(tags.id SEPARATOR ',') as tagsList
FROM posts
INNER JOIN termRelations ON ( posts.id = termRelations.postId )
LEFT JOIN cats ON ( termRelations.termId = cats.id AND termRelations.termTypeId = 1 AND cats.id =5 )
LEFT JOIN tags ON ( termRelations.termId = tags.id AND termRelations.termTypeId = 0 AND 
   (tags.id =2
   OR tags.id =1)
)
GROUP BY posts.id
LIMIT 0 , 30

I've made a couple of other changes to your original query like changing the first join to an INNER JOIN and adding the cats/tags filters to the JOIN conditions for the relevant tables.

ps when you say you have separate tables for cats and tags to speed up generating lists, you may find that one table that is correctly indexed would be just as fast and would also simplify your code.