Best structure for a relational database with articles and tags

Question

Hi there.

Got a fairly straightforward system:

Table of articles, table of tags, table of article_tags containing the IDs of the tag and the article it's assigned to.

When I want to grab a list of all of my articles and all of their tags, I have to do some GROUP_CONCAT() querying to get a comma-separated list of the tags, which I can break apart and display.

This is getting slower to load on bigger pages and I'm worried it's not the best way to do this. I asked a question recently about getting all articles with a specific tag, and ended up going for a solution using double joins. This query runs quickly, but a "general" query (eg grabbing the newest 10 articles along with all their tags) takes up to 1 second to run.

My tables are indexed pretty well and other queries run quickly. Is there a better way / design pattern for this kind of SQL structure? Any tips would be really useful.

Thanks, Matt

Answer 1

trying to return all the tags in the group_concat is almost certainly the bottleneck.

is there an issue with returning all the rows in the right order, and repeating the articles?

Answer 2

The table structure is correct, the best approach would be to analyze the execution plan of the query using the EXPLAIN syntax, and post it if you need help with it.

Answer 3

Maybe you can double the system by pre-computing the group_concat in another field of the article table and adding a FULLTEXT index on it.

I'm not sure on what the performances would be, but that's an easy thing to try. Simple queries will run through the usual process (which would remain faster) and more complex can run this way (which would be faster than the joins).