Fast large updates with MySQL

While working on my website, www.tech-outlet.com (not up yet), I realized that the database I received from my wholesaler had very abbreviated product descriptions. For instance, a computer system would have a description like “P3 600 128MB O/B SND TNT2 VIDADPT ETH W98”.. and there are over 100,000 products in this database with all kinds of these abbreviations. So I made a list of all these abbreviations and their expansions and I stuck them into a key/value array. So that earlier example would have these key/values:

$updates = array(

"P3" => "PENTIUM III",
"O/B" => "ON-BOARD",
"SND" => "SOUND",
"VIDADPT" => "VIDEO ADAPTER",
"ETH" => "ETHERNET",
"W98" => "WINDOWS 98"

);

Then I first tried issuing a mysql select for everything with the first key (P3), then a php replace() to turn it into the value (PENTIUM III), then a mysql update for every record that the select statement found. I’m not going to write out the whole code for that routine, because I found there is a LOT faster way to do it.

Basically, do a mysql SELECT REPLACE(description, " $key ", " $value "), field1, field2, and_so_on FROM the_table WHERE description LIKE '% $key %' and then use PHP’s fwrite() function to write the results to disk with commas separating the field data, newlines between records and quotes for text. Delete those records that you pulled earlier with a mysql DELETE and then do a mysql LOAD DATA to load the data from your file really fast.

Of course, you have to do this procedure for every key/value pair in the array. Currently, I have about 200 of these pairs that I replace everytime I re-install the database for updated product information. It works pretty slick…