Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: Category table recurssion

    Hi,
    I have following table structure

    Create table categories (
    cat_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    parent_id Int UNSIGNED,
    cat_name Varchar(50) NOT NULL,
    Primary Key (cat_id)) ENGINE = InnoDB;

    and I have 16 categories and 360 subcategories. On a single page I am retrieving all categories and subcategories and this requires 16 queries. Is there anyway to do this in one or two queries?

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    11
    Read up on "self joins": Join (SQL) - Wikipedia, the free encyclopedia)

    You'll want to create a regular index on parent_id also.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this article will help -- Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2010
    Posts
    4
    Quote Originally Posted by r937 View Post
    this article will help -- Categories and Subcategories
    thanks, i am going to check this now. I have one more question about indexing for instance I am running this query

    SELECT field1, field2, field3 FROM aTable WHERE field4=10

    My question is should I create index only on field4 or all 4 fields? and what if I add ORDER BY field5 how indexing will work for this?

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by moazam View Post
    My question is should I create index only on field4 or all 4 fields?
    just field4

    the ORDER BY will require a temporary file anyway

    questions like this are always difficult to answer without accurate counts of the number of rows involved

    my advice is to learn how to interpret EXPLAIN resaults
    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
  •