Selecting product brands within current category

**sxtrail** · Oct 27, 2009 12:10

Hi there

I was wondering if this query could be improved....

Basically, I have a table containing a product category tree, a table of products, a table of brands and a link table relating a product to any number of products. The products table contains a brand_id as a foreign key.

So when viewing products within a given category, I want the user to be able to filter the results by brand. So I need to select a list of brands that have products within the selected category. Here is what I have:

Code:

SELECT	 
	DISTINCT(b.brand_id),
	b.brand_name
FROM 
	brands AS b
INNER JOIN 
	products_to_categories AS ptc
	ON ptc.category_id = $catid
INNER JOIN
	products AS p
	ON p.prod_id = ptc.prod_id
WHERE
	b.brand_id = p.brand_id
ORDER BY
	b.brand_name

Can this be made more efficient or is it an acceptable solution?

Many thanks

**r937** · Oct 27, 2009 20:21

Originally Posted by sxtrail

Can this be made more efficient or is it an acceptable solution?

that's hard to say without a full understanding of your indexes and without seeing the EXPLAIN for the query

but there are several things i'd like to point out

first, DISTINCT is ~not~ a function -- it applies to all the columns in the SELECT clause, not just the one column that you've put parentheses around (which aren't necessary)

second, don't mix JOIN syntax together with join conditions in the WHERE clause

also, when you feed a parameter value into a query, it's best to feed it to the "driving" table, which should be the first table in your FROM clause, like this --

Code:

SELECT DISTINCT
       b.brand_id
     , b.brand_name
  FROM products_to_categories AS ptc
INNER 
  JOIN products AS p
    ON p.prod_id = ptc.prod_id
INNER 
  JOIN brands AS b 
    ON b.brand_id = p.brand_id
 WHERE ptc.category_id = $catid
ORDER 
    BY b.brand_name

writing the ptc table first in the FROM clause, and providing a value for it in the WHERE clause, means that you want all product brands for a certain category -- just read through the sequence of tables in the FROM clause and see if it doesn't make a lot more sense now

developing a habit to strive for clarity in the FROM clause will help you down the road when you have to understand what a query is doing if you have to make changes to it

**sxtrail** · Oct 28, 2009 05:29

Hi,

Thanks for your reply. That makes perfect sense and does read more logically. Here is the result of an EXPLAIN. Hopefully it makes sense:

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	ptc 	ref 	PRIMARY,reverse_i 	reverse_i 	4 	const 	9 	Using index; Using temporary; Using filesort
1 	SIMPLE 	p 	eq_ref 	PRIMARY,brand_id 	PRIMARY 	4 	example_co_uk_engine.ptc.prod_id 	1 	Using where
1 	SIMPLE 	b 	eq_ref 		PRIMARY 	PRIMARY 	2 	example_co_uk_engine.p.brand_id 	1

Thanks