also purchased - optimization idea

[balihr]

Not sure if I'm posting in the correct section so please move if needed...

I have a site with a large number of orders and the also_purchased module is slowing the site down. When looking at the stats, the also_purchased sql query is constantly at the top of the charts causing high memory and cpu usage.
The orders table has over 160k rows and orders_products has more than 2.7mil rows so the original query is obviously our bottleneck. I'm pretty sure this doesn't become so obvious on smaller stores, but I see potential for improvement (for everyone) and would be submitting a PR when done.

I'm considering two options (both would involve a new database table). The module would then pull data from this table instead of doing self-joins on this huge table. I'm wondering which of the 2 would be better:
#1 - creating a cron job that would update the new table daily
#2 - using an observer on checkout_success to populate the new table

Obviously both have downsides.
If it's a cron job, it's not user-friendly because one needs to set it up. I guess it could be tied in to the frontend and triggered by the first visitor of the day, but is that really the way to go? It could also be tied to the admin and rely on admin logging in, but what if the admin doesn't login for awhile, are we happy with "obsolete" data? On the other hand, if the admin doesn't login for days, it's obviously not a very busy store and this isn't really a critical feature to be up-to-date.
If using an observer on checkout_success, we're essentially slowing down the checkout process and are risking database locks at the most critical step. I know it's not a huge query and it shouldn't cause issues, but still - it's at a critical moment.

I'm thinking the observer is the obvious way to go, but would like to hear some thoughts and ideas before going for it...

[brittainmark]

I assume that you are going for a table somtehing like

CREATE TABLE also_purchased_stats (
  products_id INT UNSIGNED NOT NULL,
  related_products_id INT UNSIGNED NOT NULL,
  last_purchased DATETIME NOT NULL,
  purchase_count INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (products_id, related_products_id),
  KEY idx_lookup (products_id, last_purchased)
) ENGINE=InnoDB;

In which case I would use an observer attached to order completion to add the rows with an update on last_purchased on duplicate key.
It would also need an intial load and/or ability to reload the data.
I don't know it it would also be necessary to remove rows on product deletion. (probably overkill).

[marcopolo]

**Re: also purchased - optimization idea**

I ran with this idea and built it out as an encapsulated plugin — released on GitHub if you want to test it on your store:

https://github.com/CcMarc/AlsoPurchasedTurbo

On your #1 vs #2 question: I ended up doing both, but the observer won as the primary mechanism. It hooks `NOTIFY_ORDER_DURING_CREATE_ADDED_PRODUCT_LINE_ITEM` inside `order::create()`, so it also catches admin-created orders, not just checkout. The writes are tiny PK upserts into a pair table (products_id, also_products_id, times_purchased, last_purchased) — a 5-item order adds ~20 sub-millisecond upserts, so your checkout-lock concern turned out to be a non-issue. The cron-ish part became an admin button instead: chunked seeding from order history (resumable, auto-continues), plus rebuild/purge tools. No setup required — install, click seed, done.

The module then reads the pair table with an indexed lookup instead of the self-join, and as a bonus ranks by how often products were actually bought together instead of a random slice.

Speed test on a test store (only ~13k orders_products rows — nothing like your 2.7M): most popular product, `ANALYZE FORMAT=JSON`, warm cache:

- Stock self-join: **96.4 ms** (~13k joined rows, temp table + filesort, ~30k index lookups)
- Pair table: **0.37 ms** (750 rows off 8 InnoDB pages + 12 PK hits)

That's ~260x on a small store; the self-join cost scales with product popularity × basket size, so your numbers should be considerably more dramatic.

One thing worth knowing for your setup: if your template has a customized `includes/modules/<template>/also_purchased_products.php`, the installer won't touch it — the readme covers two options (a one-click takeover, or a small edit to keep your custom rendering on top of the pair table).

Would appreciate you throwing it at that 160k-order store and telling me how it works — and your before/after slow-query numbers if you grab them.

[balihr]

I assume that you are going for a table somtehing like

CREATE TABLE also_purchased_stats (
  products_id INT UNSIGNED NOT NULL,
  related_products_id INT UNSIGNED NOT NULL,
  last_purchased DATETIME NOT NULL,
  purchase_count INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (products_id, related_products_id),
  KEY idx_lookup (products_id, last_purchased)
) ENGINE=InnoDB;

In which case I would use an observer attached to order completion to add the rows with an update on last_purchased on duplicate key.
It would also need an intial load and/or ability to reload the data.
I don't know it it would also be necessary to remove rows on product deletion. (probably overkill).

Hey Mark!
That table structure was pretty much what I had in mind, and yes, I was gonna add a function to delete the rows when a product is deleted. But as it seems, we're not gonna find out.

[balihr]

**Re: also purchased - optimization idea**

I ran with this idea and built it out as an encapsulated plugin — released on GitHub if you want to test it on your store:

https://github.com/CcMarc/AlsoPurchasedTurbo

Wow, brilliant - thank you very much! Just had a quick look at the code and you took it not one but five steps further than what I had in mind! I'll be doing the tests on Wed or Thu and will report back - obviously not here but in the official support thread. Looks VERY promising!