It is taking xx hours to export my 10,000+ items – how to speed up product exports?
Welcome to the world of unindexed, unoptimized and non-tuned WordPress installations! 🙂
Consider when you have thousands of items, this actually results in 10’s of thousands of row in your database. Normally, all is fine with this – except for when you want to really quickly read and parse the tables (like when you want to export products! )
For example, a customer with 11,000 items had over 700,000 rows in his database tables!
So, now we have to do a little work!
1) we need to trigger mysql to start building indexes…. if you are the web person you know roughly what this means, but if not – read up here! – https://codex.wordpress.org/Database_Description
2) the index creation takes time – as it is a background process.. we have seen from a few hours to a day or so for this to fully complete
3) indexes means ALSO when new records are created, indexes have to be updated.. so large scale imports need to be managed/planned
Anyways, if any of this is beyond you – please work with your webmaster/serveradmin/hoster to make sure this is all acceptable to them
Method #1: Chunk the results:
Chunking means we just pause between creating results, give the timeouts, etc. time to reset and then move on… in this case its a simple command:
$max_custom_field = 5000 (or another number – usually 1/5th of the total population of products – don’t forget the variations!)
Method #2: Make the DB faster:
Basically, this is the indexing we discussed above..
Ask your webmaster/server operator to:
Index the meta_key column of wp_postmeta
(NOTE: We recommend you perform backups, properly plan this, etc.. it is not a common activity, however it is something we recommend for anyone experiencing timeouts,etc.)