1 query or 2 queries?

blackhorse

tables

category: category_id, name

product: product_id, category_id, name, price ... (up to dozen fields)

Given $product_id value, I want to select all the fields from prduct PLUS the category's name

I can do

1) 1 query

select product.*, category.name as catergory_name from product, category where product.category_id=category.category_id and product.product_id=$product_id

2) 2 queries

first

select * from product where product_id=$product_id;

to get all the values of product, and then after get the $category_id from this query, do the second query

select * from category where cateogry_id=$category_id

to get the category's name.

Say if category only has 1 field, product has dozens fields.

In terms of the performance, and code cleaness, should I always use 1 query like in approach 1), or approach 2) is also perfectly fine too?

Thanks!

Piranha

Personally I always try to use as few queries as possible, and I don't see any reason why you would want two queries for this.

blackhorse

I am just using it as an example. in category->subcategory->product structure, in product table I have dozens fields, some fields may have the same name as category and subcategory tables.

So to get the product table all fields values, plus the category name, and subcategory name, I have to link 3 tables together. Just to get 1 value from category and 1 value from subcategory. I don't know if this query is performancing better than I use 2-3 queries, 1 query get the products value just by select from product table, other 1-2 queries get the category and subcategory names.

If the performance is the same, or 3 queries are performancing better, I am thinking that why not 3 queries here?

I always used 1 query in this case, just wondering, is it a "rule" that in the similiar cases, I should always use 1 query rather than 2-3 queries?

Another small reasons are these small queries usually I already have it exactly the same in other places, I can copy and paste it. But the merged 1 query, usually I have to write specially. (I am not talking about this individual cases, I am talking about dozens similar cases.)

Thanks!

blackhorse

further, later on, i may have the suppliers table, the manufacturers table, on top of the product values, i will need category name, subcategory name, manufacturer name and supplier name.

Use 1 query approach, I need to rewrite that query when I add the request for manufacturer name, and then rewrite the query again for the new request of supplier name ...

But for the multiple queries approach, I only need to get the suppliers_id, manufacturer_id and then write 2 simple new queries just get the name from each table.

These queries are the most simple queries in the world. So if I use 3 or 4, 5 or 6 queries like that instead of 1 query, it wouldn't hurt the performance right? But it leaves room for the small updates.

Piranha

As I said above I always use 1 query when possible. I don't know for sure if it is performing better, but I'm like 95% sure it does. Use [man]microtime[/man] to find out for sure. And I know that many have it as a rule to use 1 query as well. But I wonder if you are actually asking about when you display one product, or when you display a list of products. When only showing 1 product it may not be that much of a gain to use one query, but when listing products people are usually doing something like this:

$result = $mysql_query(SELECT something FROM category);
foreach ($result)
{
    $result2 = $mysql_query(SELECT something FROM subcategory);
    foreach ($result2)
    {
        $result3 = $mysql_query(SELECT something FROM product);
        foreach ($result3)
        {
            do something
        }
    }
}

Let's say that you have 10 categories, each category have 10 subcategories and each subcategory have 20 products. You would in the case above do 111 queries, and not 3. And that would defenetley be bad performance.

But please don't complain about writing these easy queries again and again. It is simple queries with simple joins. In fact, it probably takes longer to ask and get an answer than it would to do the longer queries. And the queries that are long and hard is not easy to reuse, although it is the queries that you need to reuse to save some time and effort.

Piranha

blackhorse wrote:
Use 1 query approach, I need to rewrite that query when I add the request for manufacturer name, and then rewrite the query again for the new request of supplier name ...

But for the multiple queries approach, I only need to get the suppliers_id, manufacturer_id and then write 2 simple new queries just get the name from each table.

And you need to write code to validate that you get a result, and that the result is valid before doing the next query. Don't try to convince me that you would save programming time by using more queries, I don't buy it.

blackhorse

Good points! Thanks!

Yes. I was talking about 1 product cases. For the products list cases, I used to just use 1 query.

blackhorse

Piranha wrote:
And you need to write code to validate that you get a result, and that the result is valid before doing the next query. Don't try to convince me that you would save programming time by using more queries, I don't buy it.

If the product query is successful, it will offer the valid id for the manufacture, supplier, and category etc.

blackhorse

There is another approach for the products list case.

for category->product

For example, if there are dozens of thousands products, I will only use

1) 1 query to select the products. It will give the result of the category_id for each product

2) I will use another query to select the category_id and category_name from category table, and assign the category id to the array index, and category name to the array value.

3) because I have the category_id from the product select query from the step 1, I will use it as the index and get the array value from the step 2, so i can display the category name for the produt list.

So it is still only 2 queries.

Piranha

blackhorse wrote:
If the product query is successful, it will offer the valid id for the manufacture, supplier, and category etc.

No. It will offer an id that is valid at the time when the first query was made. But it is possible that the data have changed after that and before the second or third query, that will make it not valid anymore.

Anyway, sorry that I was a bit grumpy before.