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

Read up on "self joins": Join (SQL) - Wikipedia, the free encyclopedia)

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

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

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