At Globalgraphics we build a lot of e-commerce websites, these often have lots of products within them which a lot of the time we import from a CSV.

Because all products needed to be inserted one-at-a-time into the database (because each record needed to have certain things calculated) we couldn’t do a simple mysql command “LOAD DATA LOCAL” as this simply dumps the contents into a table. The usual practise then was to open the CSV and recurse through it, taking each record out of the CSV at a time using the PHP “fgetcsv”.  This works great if you have a few hundred records, it imported at a rate of about 0.6 seconds per record. The problem came when we were building a website which has 500,000 products in the CSV. This would take about 83 hours which is way too long.

The answer we came up with was to use the mysql command “LOAD DATA LOCAL” (as this take a few seconds to import) to import the CSV into a temporary table.  Once this has been imported it is then very quick to recurse through the temporary table, doing the necessary calculations, and inserting the products into the final table.

This process managed to take the timings from 0.6 seconds per record to approximately 0.001 seconds per product. Now the import process takes a little over 8 minutes, a vast improvement on 83 hours.

• Retweet
• 
• 
• 
• 
•