Unfortunately, these patches cannot be used anymore since Drupal 10.1. At least none of them which drops the primary key:
New transaction isolation level default for new sites for MySQL, MariaDB or equivalent databases →
Rewinding the iterator indeed a major performance issue on large data sets, but it can be circumvented by enabling [high water mark]( https://www.drupal.org/node/1223936 → ) check or adding its support to your custom source plugin based on [SqlBase::initializeIterator()](https://git.drupalcode.org/project/drupal/-/blob/11.x/core/modules/migra...).
Here is a nice article about the topic: [Speed up Your Drupal Migrations with Highwater Marks](https://drupalize.me/blog/speed-your-drupal-migrations-high-water-marks).
This problem affects me as well. Thank you @benjifisher very much for the investigation and root cause analysis. I run into this in the past could of days after a `8.x-1.27` => `8.x-1.30` update. I also realized that indexing got very very slow. In my case it takes more than 1 minute to index just 50 items but previously (with `8.x-1.27`) I didn't have problems.
I also use the HTML filter with the default settings and I need to index documents including around a couple of hundreds to several thousands of HTML tags (per document!).
I applied the patch from #9: let a user specify if her blog entries should be votable → ( https://www.drupal.org/project/search_api/issues/3388678#comment-15294371 🐛 Performance issues after fixing 'PHP 8.1 preg_replace()' issue Needs review ) on `8.x-1.30` and it solved the problem for me. Now indexing is fast enough again. Thank you @drunken monkey!
Thanks for the patch in #4, it works.