You are here:  » Using searchresults as related products


Using searchresults as related products

Submitted by macland on Mon, 2020-05-25 10:56 in

Hi David

My site has 1,8 million products, so I had to disable related products for all products, including 404-pages, in order to keep the sql server afloat.
(I have a lot of traffic, with multiple sites using the same database).

But I miss the related products, so I've been thinking about how it could be optimized.
Do you think any of the following could be a good solution?

1) Build a related products cache and run it after import.

2) Search seems to provide a result without to much sql load, I think?
If I'm right in that, I could include the searchresults for the first word of the product name? I have tried manually testing and it provides okay results most of the time.

3) Just display products from the same merchant, regardless of relevance

Do you think any of these would be possible, and provide products without closing down my server?

One more thing.

Since I have a lot of sites sharing the same code, it would be cool if the results could be randomized.
So fx. if there are 100 products that fit the criteria, 20 random is printed.

Would that also be possible?

Thank you in advance for your input :-)

Submitted by support on Mon, 2020-05-25 12:20

Hi,

If you disabled the product specific WHERE clauses (other than not equal to the same product) the Related Product query is essentially identical to a normal search for the product name so that might be worth trying first. To give that a go, edit products.php and look for the following code beginning at line 86:

    $wheres = array();
    if (isset($product))

...and REPLACE with:

    $wheres = array();
    if (isset($product))
    {
      $wheres[] = "name <> '".database_safe($product["products"][0]["name"])."'";
    }
    if (FALSE)

And then to randomise the results, look for the following code at line 148:

      $related = true;

...and REPLACE with:

      shuffle($searchresults["products"]);
      $related = true;

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by macland on Tue, 2020-05-26 08:10

Thank you David. I've changed the code a small bit with regards to the shuffle parts
(the first sql lookup has a limit of 40, then I shuffle and slice $rows to 20 before the sql2 lookup)

I have implemented this on some of my sites, and then I will see how my server handles it :-)

Submitted by macland on Wed, 2020-05-27 07:20

Hi David

Thank you but I can already see now with 5 sites that it's putting to heavy a load on my server with just 5 sites - and I have around 120 sites sharing the same db.

What I think would be smart would be to cache the related products, also since 120 sites will use that info, so it's better to just calculate it once.

I thought something along the lines of:
- adding an extra "related products" column in the products table.
- make a product_cache_script I can run after import
- the script then runs the normal related product query, adding X product ID's (fx. 40) to the new related_products field.
- in my products.php I print X random products (fx. 20, or half the length of the ID's array)

Would that be an optimized way do to this? Or would there still be some ressource drain and areas to improve?
I'm pretty new to looking at databases, so I'm not quite sure which queries require what :-)

Submitted by support on Wed, 2020-05-27 11:47

Hi,

Caching the related products at import time (or rather as a post-import script) is technically straight forward - the only question mark would be the time taken and "all at once" load on the database to do that.

There was one other thought I had which might be worth giving a go quickly first (removing the actual product name exclusion and taking it out from the result set post-query) as this may just be causing the query to run sub-optimally in these circumstances.

If you could perhaps email me your current products.php and I'll look into it further with you - it might be worth making some EXPLAIN queries to check how the database is dealing with the SQL...

Cheers,
David.
--
PriceTapestry.com