Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Unanswered: Ideal Select statement?

    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

    Code:
    Select distinct 
    articles.article_id,
    articles.author_id,
    articles.penname_id,
    articles.category_id,
    articles.article_title,
    articles.posteddate
    From
    prefix_article_tags AS tags
    ,
    prefix_articles AS articles
    Where
    tags.tag_id = '1330' AND
    articles.`status` = 'A' AND
    tags.`status` = '1'
    Order By
    articles.posteddate Desc
    Limit 0, 25

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •