Speed up this query

[Chris]

Alright... I have a need to pull data from a table, do some minor processing on it, and spit it out in CSV form.

here is my query:

SELECT product.product_id, product.sku, product.main_image_url, product.title, product.stock_level, product.price, product.description, manufacturer.name AS mname, category.name AS cname
FROM product, manufacturer, category, category_product
WHERE product.manufacturer_id = manufacturer.manuf_id
AND product.product_id = category_product.product
AND category_product.category = category.cat_id
GROUP BY product.product_id
LIMIT 0 , 300

Here is what phpmyadmin spits out:

Showing rows 0 - 299 (662 total, Query took 601.2562 sec)

The query takes so long that if I run it outside of phpmyadmin the page always times out.

Anyone know a way to make it at all faster?

here is what explain spits out:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE manufacturer ALL NULL NULL NULL NULL 42 Using temporary; Using filesort
1 SIMPLE category ALL NULL NULL NULL NULL 92
1 SIMPLE product ALL NULL NULL NULL NULL 1559 Using where
1 SIMPLE category_product ALL NULL NULL NULL NULL 1629 Using where

[Chris]

New problem...

I need to get a print out of the complete category tree for these products with this 1 query, else I'll be doing it in the php calling multiple nested mysql queries and thats inefficient.

I have this:

select product.product_id , product.sku , product.main_image_url , product.title , product.stock_level , product.price , product.description , manufacturer.name as mname , category.name as cname, category.cat_parent_id as pid, (Select category.name as from category where cat_id = pid) as pname from product , manufacturer , category , category_product where product.manufacturer_id = manufacturer.manuf_id and product.product_id = category_product.product and category_product.category = category.cat_id group by product.product_id

Which gives me the primary category, and the parent category, but not the parent's parent, or the parent's parent's parent. Not all will be the same depth. I feel like I'm bumping up against the limits of my sql knowledge.

[Chris]

Well... here is what I got... talked about being nested... It works, but I can't see it being efficient, opinions?

SELECT product.product_id, product.sku, product.main_image_url, product.title, product.stock_level, product.price, product.description, manufacturer.name AS mname, category.name AS cname, category.cat_parent_id AS pid, (

SELECT category.name
FROM category
WHERE cat_id = pid
) AS pname, (

SELECT category.name
FROM category
WHERE cat_id = ( 
SELECT category.cat_parent_id
FROM category
WHERE cat_id = pid ) 
) AS ppname, (

SELECT category.name
FROM category
WHERE cat_id = ( 
SELECT category.cat_parent_id
FROM category
WHERE cat_id = ( 
SELECT category.cat_parent_id
FROM category
WHERE cat_id = pid ) ) 
) AS pppname
FROM product, manufacturer, category, category_product
WHERE product.manufacturer_id = manufacturer.manuf_id
AND product.product_id = category_product.product
AND category_product.category = category.cat_id
GROUP BY product.product_id

[rpanella]

Ok, so what you are trying to do is spit out on a line the data for each product and then on the same line list its category, followed by its parent, all the way up to the root?

The best way I can think to do this is to use 2 queries. With the first you pull all the category data and store it in a PHP array. Something like this:

$result = mysql_query("SELECT catid,parentid,name FROM categories");
while ($r = mysql_fetch_row($result)) {
     $parentof[$r[0]] = $r[1];
     $nameof[$r[0]] = $r[2];
} 

Then you do the first query which pulls the product data (although I would use left joins as I think they would be much faster in this case). It will give you the category of the product and then you can do a while loop to go up the category tree

while ($parentof[$catid]) {
     echo "," . $nameof[$parentof[$catid]];
     $catid = $parentof[$catid];
} 

That will list all the categories up until it hits root, for which parentof will not contain anything.
________
MAZDA 929 COUPE

[rpanella]

Actually, in looking at your table structure, it looks like you actually have an many to many relationship between products and categories where products can belong to multiple categories in the tree. So are you trying to print out the path for each of the categories it belongs to?
________
MEDICAL MARIJUANA SEEDS

[Chris]

Yes... hoping to do it in 1 query for efficiency. It works like the above more or less.

Though I ran into another issue now... I was going to use a preexisting import script after I get this data but it doesn't quite work right so I'll have to make my own tommorow.

[rpanella]

That is really more than 1 query since there are so many nested sub queries, each of which has to run for each result of the main query.

Although if the number of rows are small enough, and you won't be running it very often, it shouldn't matter I guess.

What is the runtime of that query?
________
Handjob Femdom

[Chris]

Showing rows 0 - 650 (651 total, Query took 0.1594 sec)

Not so bad.

I actually figured out the problem with the first one. The guy who setup this database for me didn't appropriate index things, so I had to go through and build the necessary indexes. That solved things.