Also Purchased Turbo - support thread

Also Purchased Turbo is a new encapsulated plugin that replaces the stock "Customers who bought this product also purchased" engine with a precomputed product-pair table.

The stock module runs an orders_products self-join on every product page view. The cost grows with order history and product popularity, and on large stores it's often the most expensive query in the slow-query log. This plugin does that work once - a checkout observer keeps a small pair table current in real time, and product pages read it with an indexed primary-key lookup instead.

In testing, the also-purchased query ran roughly 260x faster (96 ms down to under 1 ms on the store's most popular product), and the gain grows with store size. As a bonus, recommendations are ranked by real purchase affinity - the products most often actually bought together - with Recency and Random modes available.

Highlights:

• No core file changes, no template changes - your existing also-purchased presentation is preserved
• Installs via Plugin Manager; a chunked, resumable seeding tool builds the pair table from your existing order history
• Honors the standard display settings (min/max/columns)
• Customized template modules are detected and never overwritten - the readme covers a one-click takeover or a small integration edit that keeps your custom rendering
• Admin pages under Configuration and Tools (status, seeding, maintenance, debug log)

Requires Zen Cart 2.2.2 or later.

Download and source: https://github.com/CcMarc/AlsoPurchasedTurbo
(Plugin library listing pending approval.)

Credit where due: the idea came out of balihr's "also purchased - optimization idea" thread in General Questions.

Feedback welcome - especially from larger stores. If you try it, before/after numbers from your slow-query log would be great to see in this thread.

Thanks for doing this!

Marc, this is phenomenal!

Sorry it took me so long to reply - I wanted to make sure I can test it properly. Before I go into details, I would wholeheartedly suggest this plugin being distributed as part of core code because the difference it has on performance is not measured in inches, this is miles better!

So, seeding took awhile, which is perfectly fine - it's a fully automated process and keeps the session alive so I believe that part was handled beautifully. The only "downside" I can notice is the resulting size of this new table - it's now a massive 4.5 GB table with more than 40 mil rows. Of course, this has nothing to do with you or the plugin - it's a busy store with lots of orders and lots of purchased products so of course there will be a lot of data.

Here's the numbers:
orders: 167k rows
orders_products: 2.7mil rows

TEST #1 - random product
original APP module
parse time: 0.26820 seconds
query cost: 1,313,034.00
rows examined: 2,562,922

APP TURBO module
parse time: 0.01435 seconds
query cost: 171.30
rows examined: 234

TEST #2 - most popular product (purchased 46k times)

original APP module
parse time: 0.78495 seconds
query cost: 1,314,236.86
rows examined: 2,562,922

APP TURBO module
parse time: 0.19134 seconds
query cost: 5149.34
rows examined: 17,278

These results are impressive. In the first test, we have a query that is almost 8000 times more efficient, plus it doesn't use temporary tables and filesort. The second benchmark is equally impressive, although the numbers seem a bit higher, but again, on a very popular product. If we convert it into numbers, this is a 99.6% drop in costs, meaning a huge relief on the CPU and memory. WOW! Just wow!

I was gonna build the plugin myself, but I very much doubt I'd make it THIS good - this is an absolutely brilliant and well built plugin and you've done a superb job! Thank you so much!

balihr:

Marc, this is phenomenal!

Sorry it took me so long to reply - I wanted to make sure I can test it properly. Before I go into details, I would wholeheartedly suggest this plugin being distributed as part of core code because the difference it has on performance is not measured in inches, this is miles better!

So, seeding took awhile, which is perfectly fine - it's a fully automated process and keeps the session alive so I believe that part was handled beautifully. The only "downside" I can notice is the resulting size of this new table - it's now a massive 4.5 GB table with more than 40 mil rows. Of course, this has nothing to do with you or the plugin - it's a busy store with lots of orders and lots of purchased products so of course there will be a lot of data.

Here's the numbers:
orders: 167k rows
orders_products: 2.7mil rows

TEST #1 - random product
original APP module
parse time: 0.26820 seconds
query cost: 1,313,034.00
rows examined: 2,562,922

APP TURBO module
parse time: 0.01435 seconds
query cost: 171.30
rows examined: 234

TEST #2 - most popular product (purchased 46k times)

original APP module
parse time: 0.78495 seconds
query cost: 1,314,236.86
rows examined: 2,562,922

APP TURBO module
parse time: 0.19134 seconds
query cost: 5149.34
rows examined: 17,278

These results are impressive. In the first test, we have a query that is almost 8000 times more efficient, plus it doesn't use temporary tables and filesort. The second benchmark is equally impressive, although the numbers seem a bit higher, but again, on a very popular product. If we convert it into numbers, this is a 99.6% drop in costs, meaning a huge relief on the CPU and memory. WOW! Just wow!

I was gonna build the plugin myself, but I very much doubt I'd make it THIS good - this is an absolutely brilliant and well built plugin and you've done a superb job! Thank you so much!

v1.1.0 released - pair pruning

balihr, thank you for the thorough testing - those numbers are the first real large-store validation this plugin got, and your one "downside" turned directly into this release.

You were right that the table size is the trade-off: your store's big baskets mean huge pair fan-out, and your most popular product alone was carrying 17k+ pair rows, while the storefront only ever displays a handful. Storing every historical pair is wasted space, and it's also exactly why your second benchmark examined 17,278 rows.

So v1.1.0 adds configurable pair pruning:

• New setting: maximum pairs stored per product (default 50, 0 = unlimited). The table is capped at that limit per product, and the per-product read shrinks to the kept set.
• A chunked "Prune pair table" tool that continues automatically, same as seeding - and pruning now runs automatically right after seeding completes.
• Survivors are chosen by your configured ranking, so what the storefront displays doesn't change.
• The Tools page now shows the last prune: when it ran and the before/after row counts.

For your store with pruning it should benchmark like Test #1 across the board. The observer keeps writing freely so new pairs can still climb in; pruning is periodic maintenance like the seed.

Download: AlsoPurchasedTurbo v1.1.0 (also submitted to the plugin library)

Upgrading from 1.0.0: use Plugin Manager's Upgrade button (a new configuration setting is seeded), then click "Prune pair table" once to trim your already-seeded table.

Thanks again - this is a better plugin because you kicked the tires properly.

Now, this is absolutely brilliant!

After pruning the table, benchmark results are even more impressive! The table has gone down to 2.3mil rows and 2.8 GB (from 40mil and 4.5 GB) and the improvement is also obvious in the frontend.

TEST #2 - most popular product (purchased 46k times)

original APP module
parse time: 0.76543 seconds
query cost: 1,314,236.86
rows examined: 2,562,922

APP TURBO 1.0.0 module
parse time: 0.19134 seconds
query cost: 5149.34
rows examined: 17,278

APP TURBO 1.1.0 module
parse time: 0.03009 seconds
query cost: 36.59
rows examined: 50

Parse time was measured ONLY for the APP module, meaning it starts at the top of includes/modules/also_purchased_products.php and ends at the bottom of it.
So, let's just compare parse time and query cost...
APP Turbo is 96% faster and has 99.997% less cost on the server resources! This is not optimization, this is an absolute rescue of server resources!
From a query that was a major bottleneck choking a dedicated server, you've managed to turn it into something that the server doesn't even flinch when running it. I dare say this is now enterprise-level code and by far the best optimization result I've seen in ages! Absolutely amazing!!!

balihr:

Now, this is absolutely brilliant!

After pruning the table, benchmark results are even more impressive! The table has gone down to 2.3mil rows and 2.8 GB (from 40mil and 4.5 GB) and the improvement is also obvious in the frontend.

TEST #2 - most popular product (purchased 46k times)

original APP module
parse time: 0.76543 seconds
query cost: 1,314,236.86
rows examined: 2,562,922

APP TURBO 1.0.0 module
parse time: 0.19134 seconds
query cost: 5149.34
rows examined: 17,278

APP TURBO 1.1.0 module
parse time: 0.03009 seconds
query cost: 36.59
rows examined: 50

Parse time was measured ONLY for the APP module, meaning it starts at the top of includes/modules/also_purchased_products.php and ends at the bottom of it.
So, let's just compare parse time and query cost...
APP Turbo is 96% faster and has 99.997% less cost on the server resources! This is not optimization, this is an absolute rescue of server resources!
From a query that was a major bottleneck choking a dedicated server, you've managed to turn it into something that the server doesn't even flinch when running it. I dare say this is now enterprise-level code and by far the best optimization result I've seen in ages! Absolutely amazing!!!

Thank you for the re-run, those numbers made my day. Rows examined: 50 is my favorite part, that's exactly the pair limit, so the read path is now doing the minimum work possible.

One catch in your stats: the 2.8 GB is not real. InnoDB never returns deleted rows' space to the OS, it stays inside the tablespace as internal free space, so after a big prune the file stays bloated. The math says your 2.3 mil rows should be a few hundred MB at most. One statement fixes it:

OPTIMIZE TABLE products_also_purchased;

It rebuilds the table with only live rows (a couple minutes at your size, storefront keeps working during it).

That gap between reported and real size will hit every large store that prunes, so I just released v1.2.0 with it built in: an "Optimize table (reclaim disk space)" button on the Tools page that shows how much is reclaimable before you click and reports what it recovered after, plus "Last prune" and "Last optimize" history in the status panel. After a big prune it now nudges you toward the optimize automatically.

AlsoPurchasedTurbo v1.2.0 (upgrade via Plugin Manager, a new setting is seeded)

Thanks again for the feedback!

Also Purchased Turbo v1.2.0 released.
New in this version:

• "Optimize table (reclaim disk space)" maintenance action. After a big prune, InnoDB keeps the deleted rows' space inside the tablespace instead of returning it to the OS, so the file on disk stays large. This rebuilds the table with only live rows - it shows how much space is reclaimable before you run it and reports what it recovered after.
• The Tools status panel now shows table size on disk, plus "Last prune" and "Last optimize" history (when they ran, before/after counts, space reclaimed).
• After a prune that removes a large number of rows, a message points you to the optimize action.

Download: AlsoPurchasedTurbo v1.2.0

Upgrading from 1.0.0 or 1.1.0: use Plugin Manager's Upgrade button (new configuration settings are seeded), then run "Prune pair table" once if you have not pruned yet.

Note for the moderators: v1.1.0 was submitted to the plugin library but not yet approved - please disregard that submission in favor of v1.2.0, which supersedes it and combines what is new.

Nice one! Yeah, I wasn't paying attention to that, but the size didn't really bother me, it was just part of the report.

Interesting enough, the OPTIMIZE didn't work, not even from phpmyadmin, so I had to copy the data into a temporary new table, drop the original and then renamed the temporary into "products_also_purchased". It went down to 78 MB, but then I ran the action from the plugin admin and it worked, removed another 0.8 MB. Magic!

balihr:

Nice one! Yeah, I wasn't paying attention to that, but the size didn't really bother me, it was just part of the report.

Interesting enough, the OPTIMIZE didn't work, not even from phpmyadmin, so I had to copy the data into a temporary new table, drop the original and then renamed the temporary into "products_also_purchased". It went down to 78 MB, but then I ran the action from the plugin admin and it worked, removed another 0.8 MB. Magic!

Nice end results!

I think the reason OPTIMIZE failed from phpMyAdmin is that at 2.8 GB the rebuild takes minutes in one statement, and phpMyAdmin runs under the web server's PHP timeout, so the connection died and MySQL rolled back the half-finished rebuild. It can also sit waiting on a metadata lock behind the store's normal traffic and burn its whole timeout doing nothing. Best part: your copy/drop/rename workaround IS what OPTIMIZE does internally on InnoDB, a recreate and swap. You did not find an alternative, you ran the same operation by hand in pieces too quick to kill. The plugin button then had it easy, rebuilding an already-78 MB table takes seconds.

So the complete arc from your store: 40M rows / 4.5 GB unpruned, down to 2.3M rows / 78 MB pruned and optimized, and the popular-product read from 2.56 million rows examined to 50.

One last ask: may I put your benchmarks in the readme, credited to you? Your numbers are the whole case for the plugin.

Yeah, wasn't a workaround, just a manual method of doing the optimize. :smile:
I don't think it was related to timeouts since the process was completing after several seconds. This time is also not very realistic for such size so there must've been something else. Truth be told - I don't care, the end result is all that matters.

As for benchmark results - absolutely! It may be my numbers, but it's your results - you have absolute proof of your great work! :cheers:

P.S. Possibly worth mentioning in case someone is using it with an older template and still looping the results using "while" - the code in includes/modules/YOUR_TEMPLATE/also_purchased_products.php needs to be updated. Even the latest ZC still uses while in that module, so the new code won't work and will only display one single product because we're using MoveNextRandom() on $db->Execute().
Using ZC 2.2.2 as example:

while (!$also_purchased_products->EOF) {
            $product_info = new Product((int)$also_purchased_products->fields['products_id']);
            $data = array_merge($also_purchased_products->fields, $product_info->getDataForLanguage());

            $list_box_contents[$row][$col] = [
                'params' => 'class="centerBoxContentsAlsoPurch"' . ' ' . 'style="width:' . $col_width . '%;"',
                'text' => ((empty($data['products_image']) && (int)PRODUCTS_IMAGE_NO_IMAGE_STATUS === 0) ? ''
                        : '<a href="' . zen_href_link(zen_get_info_page($data['products_id']), 'products_id=' . $data['products_id']) . '">'
                        . zen_image(DIR_WS_IMAGES . $data['products_image'], $data['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT)
                        . '</a><br>')
                    . '<a href="' . zen_href_link(zen_get_info_page($data['products_id']), 'products_id=' . $data['products_id']) . '">' . $data['products_name'] . '</a>',
            ];

            $col++;
            if ($col > (SHOW_PRODUCT_INFO_COLUMNS_ALSO_PURCHASED_PRODUCTS - 1)) {
                $col = 0;
                $row++;
            }
            if ($apt_active) {
                $also_purchased_products->MoveNext();
            } else {
                $also_purchased_products->MoveNextRandom();
            }
        }

balihr:

Yeah, wasn't a workaround, just a manual method of doing the optimize. :smile:
I don't think it was related to timeouts since the process was completing after several seconds. This time is also not very realistic for such size so there must've been something else. Truth be told - I don't care, the end result is all that matters.

As for benchmark results - absolutely! It may be my numbers, but it's your results - you have absolute proof of your great work! :cheers:

P.S. Possibly worth mentioning in case someone is using it with an older template and still looping the results using "while" - the code in includes/modules/YOUR_TEMPLATE/also_purchased_products.php needs to be updated. Even the latest ZC still uses while in that module, so the new code won't work and will only display one single product because we're using MoveNextRandom() on $db->Execute().
Using ZC 2.2.2 as example:

while (!$also_purchased_products->EOF) {
$product_info = new Product((int)$also_purchased_products->fields['products_id']);
$data = array_merge($also_purchased_products->fields, $product_info->getDataForLanguage());

        $list_box_contents[$row][$col] = [
            'params' => 'class="centerBoxContentsAlsoPurch"' . ' ' . 'style="width:' . $col_width . '%;"',
            'text' => ((empty($data['products_image']) && (int)PRODUCTS_IMAGE_NO_IMAGE_STATUS === 0) ? ''
                    : '<a href="' . zen_href_link(zen_get_info_page($data['products_id']), 'products_id=' . $data['products_id']) . '">'
                    . zen_image(DIR_WS_IMAGES . $data['products_image'], $data['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT)
                    . '</a><br>')
                . '<a href="' . zen_href_link(zen_get_info_page($data['products_id']), 'products_id=' . $data['products_id']) . '">' . $data['products_name'] . '</a>',
        ];

        $col++;
        if ($col > (SHOW_PRODUCT_INFO_COLUMNS_ALSO_PURCHASED_PRODUCTS - 1)) {
            $col = 0;
            $row++;
        }
        if ($apt_active) {
            $also_purchased_products->MoveNext();
        } else {
            $also_purchased_products->MoveNextRandom();
        }
    }

Readme is updated with your full arc, thank you again for the green light: all three benchmark generations on the popular product (0.785s stock, 0.191s unpruned, 0.030s pruned with rows examined at exactly the pair limit) plus the 4.5 GB down to 78 MB storage line, credited to you.

And great catch on MoveNextRandom, that one matters. The stock module fetches with ExecuteRandomMulti, whose result object is built for MoveNextRandom; the plugin's query is a plain Execute, so MoveNextRandom on it dies after one row, exactly as you found. One refinement to your conditional: key it off whether the pair table actually supplied the result rather than whether the plugin is active. If the plugin is active but a product has no pairs yet and the stock fallback runs, that result is random-multi again and needs MoveNextRandom. The readme now documents the exact pattern with a flag set after the fallback block, credited to you for the catch.

Thanks for being the best tester a plugin could ask for. This was a fun plugin!