Reply to thread

Had to fix the errors on the inital LOAD INFILE to the database.tomtop_feed table. Something went wrong and since this feed is so specific to this vendor with all the COLLATE=utf8mb4_0900_ai_ci errors, well China ya know

[ATTACH=full]17764[/ATTACH]

Against share A Sales advice; I am going to use the

PRIMARY KEY (`id`), <<<the row numbers as the primary key

KEY `ProductID` (`ProductID`) <<<my index key

this command will create indexes

this is the slow but sure way. it took 0.12 to 0.32 seconds to find and update each row; or about 5 hrs for the 107,208 rows

[CODE]ALTER TABLE `tomtop_feed` ADD INDEX `ProductID` (`ProductID`);[/CODE]

[ATTACH=full]17765[/ATTACH]

as so

here is why

[ATTACH=full]17767[/ATTACH]

id     | ProductID  | link                                                                                     |

+--------+------------+------------------------------------------------------------------------------------------+

|  60813 | 970339645  |//////////////////////////?merchantID=27868&userID=2621106&productID=970339645

as you can see my ProductID(index) matches the &productID= of the link so that will be a good index for the data

the problem with using the id column is that that id my be deleted or possible changed for some reason (usually a revision of some kind and a new row insert statement).

there are sql (reserved) control characters in the URLs and as I said above there were errors "I made" so the repetitive REPLACE nested part of the above statement fixes the mess and returns real urls.

Nested like  HTML tags

<div>

<div>

<div>

</div></div></div>

same idea