I have latest magento 1.9 shop with 2000 configurable products and each product have approximately 30 associated products (apparel store with sizes and colors as configurable options). Total 60.000 products. I have two store views. My VPS server has 4 cores, 2GB ram, PHP 7 and Percona 5.6 MySQL server optimized with mysqltuner.
If I truncate
core_url_rewrite table, URL reindex is finished in few minutes. But if URL Rewrite reindex is started again, without truncate, it can take up to few hours or it does not finish at all due to PHP fatal error, memory exhausted (1GM of ram in limit for php).
A strange thing is, that I see a mysql process doing this:
SELECT MAX(CAST(SUBSTRING(`request_path`, 24, LENGTH(`request_path`) - 23 - 5) AS SIGNED)) FROM `core_url_rewrite` WHERE (store_id = '1') AND (request_path LIKE 'james-amp-nicholson-jn-%.html') AND (request_path REGEXP '^james\\-amp\\-nicholson\\-jn\\-[0-9]*\\.html$')
almost all the time of url reindex process. The mysql transaction is filling very slowly and I assume that this is taking a lot of ram. I have around 20.000 (1000 configurable products with 20 associated products) products named like this:
James & Nicholson | JN XYZ
Any suggestions on speeding or solving this problem?