PHP taking too long to process this SQL. Any SQL experts here?

[AndyUK]

I am importing a product list from an XML file. The supplier who built the XML file outputs every variation of a product, as a separate product. For example, a T-shirt which has three different sizes and three different colours, is listed in the XML files as 9 separate products, rather than 1 T-shirt with 9 options. I need to try link them together and the best way I can think of is to build a hierarchy.

This is how I have built it using PHP and MYSQL, but it takes forever to process. I’m 99.9999% certain that SQL could do this job directly in a few seconds. The problem is I don’t know to find the children and add them.

Any SQL experts here who can help with this?

// Add parent product into hierarchy
$stmt = "INSERT INTO ProductHierarchy (product_id, is_parent) SELECT product_id, 1 FROM ActiveProducts WHERE product_id NOT IN (SELECT product_id FROM ProductHierarchy) GROUP BY name";
$result = mysqli_query($sql_connect, $stmt);

// Get list of parents from hierarchy
$stmt = "SELECT ph.product_id, ap.name FROM ProductHierarchy ph INNER JOIN ActiveProducts ap ON ph.product_id = ap.product_id";
$result = mysqli_query($sql_connect, $stmt);

while ($row = mysqli_fetch_assoc($result)) {

    // Add new child to parent        
    $substmt = "INSERT INTO ProductHierarchy (product_id, is_child, parent_id) SELECT product_id, 1, '" . $row['product_id'] . "' FROM ActiveProducts WHERE name = '" . $row['name'] . "' and product_id NOT IN (SELECT product_id FROM ProductHierarchy)";
    $subresult = mysqli_query($sql_connect, $substmt);
    
}

[DaveMaxwell]

A big hurdle in your logic is your first SQL statement is going to add EVERY record in ActiveProducts into ProductHierarchy, regardless of whether it’s a parent ID.

A better approach would be to handle this while importing the records initially. As it comes in, search the product hierarchy for that product name. If found, set the is_child and set the parent_id to that found record value, otherwise set the is_parent accordingly (note: You could just have one boolean - it’s either a parent or it’s not. If it’s not, then logically, it’s a child)

[AndyUK]

Hi Dave,

Data originally comes from the XMLimport and is moved into ActiveProducts using a INSERT INTO SELECT x,x,x,x FROM bla WHERE bla. I tried doing this logic in PHP & MySQL and it took 20 odd minutes to run so just had SQL do it directly. The same problem with building the hierarchy, I don’t know how to do that directly in SQL.

[AndyUK]

Products are import from an XML file into XMLImport. From here new products are moved into ActiveProducts like so…

INSERT INTO ActiveProducts (name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,dateadded,updated)
SELECT name,type,size,colour,sku,weight,instock,quantity,price,image1,image2,image3,image4,image5,image6,description,NOW(),1
FROM XMLImport WHERE sku NOT IN (SELECT sku FROM ActiveProducts)

What I want to do here is say…

If name doesn’t already exist in ActiveProduct, then Parent_ID = NULL
Else, Parent_ID = Product_ID (of the first record returned ordered by product_id)