I have a system in place on my site that takes the primary keywords from an article and places them in a table, then in another table references which article has which tags.

The tables are setup like this

Table [tags]
id(unique id)
tag(unique)
count(number of articles that contain that word

Table [article_tags]
article_id
tag_id
status

Table [articles]
article_id
author_id
penname_id
category_id
article_title
posteddate

I have been trying to use the following SQL statement to get the article details of articles that relate to the given tag_id however it takes forever, and doesnt even give the ones that are actually the right ones. In this case I was using tag id 1330


Any ideas of how to make it faster and accurate? If a join would be better could someone provide an example, as I know next to nothing about joins.


Thanks for any help in advance,

Mike

my advice: ditch the tag_id column

you can keep the tags table, because it is still needed for referential integrity as well as the count

in the article_tags table, use the tag itself

in your query, make sure you reference the correct table for the status column, and please remove DISTINCT

as for performance, what indexes have you declared?