Normally, you get the collection, update the products while looping through it. But when the collection is over a thousand products, this becomes very inefficient. The average time it takes to save a product this way usually takes 1-2 seconds on my workstation. However, execution time can add up very quickly.
Then… I saw someone who was using a direct SQL call to update the product category’s position. Hm… definitely a good idea to bypass all overheads, even though it’s somewhat “hacky”.
Here is what I came up after experimenting with it a bit:
The products showed up on the correct category after it ran and nothing seemed broken after running the indexer. It took a fraction of the run time when comparing to the original method. I think it’s working well. 🙂 Try it out for yourself!