Exporting from WordPress and WooCommerce can be slow due to how imports are performed from plugins – typically one item at a time.

If you know SQL, you’ll know that SQL queries are many orders of magnitude faster at exporting this data if you can get the SQL query right.

In this guide, I’ll show a fast query which can be used to grab data from wp_posts, wp_postmeta and the taxonomy tables with as few joins as possible making this the fastest query you can run to export this data.

Construct your SQL

You need to know which fields you wish – in this SQL below, I grab a number of columns from wp_posts, some from postmeta and then a bunch of taxonomy information too. Alter the taxonomy and meta key entries below to grab what you need from the database for your product feed export. You can safely delete rows where you don’t need them. You should also update your output file location for your exports.

SELECT 
    p.ID,
    p.post_title,
    p.post_content as 'Product description',
    p.post_excerpt as 'Short Description',
    CONCAT('https://localhost?p=', p.ID) as 'Permalink',
    MAX(CASE WHEN pm.meta_key = '_product_url' THEN pm.meta_value END) as '_product_url',
    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) as '_regular_price',
    MAX(CASE WHEN pm.meta_key = '_sale_price' THEN pm.meta_value END) as '_sale_price',
    MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) as '_sku',
    SUBSTRING_INDEX(MAX(CASE WHEN pm.meta_key = 'external_image_url' THEN pm.meta_value END), '|', 1) as 'external_image_url',
    MAX(CASE WHEN pm.meta_key = '_wc_rating_count' THEN pm.meta_value END) as '_wc_rating_count',
    MAX(CASE WHEN pm.meta_key = '_wc_average_rating' THEN pm.meta_value END) as '_wc_average_rating',
    MAX(CASE WHEN pm.meta_key = '_wc_review_count' THEN pm.meta_value END) as '_wc_review_count',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_cat' THEN t.name END) as 'product_category',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_tag' THEN t.name END) as 'product_tags',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Größe' THEN t.name END) as 'Größe',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Lieferzeit' THEN t.name END) as 'Lieferzeit',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Versandkosten' THEN t.name END) as 'Versandkosten',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Marke' THEN t.name END) as 'Marke',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_Verkäufer-Shop' THEN t.name END) as 'Verkäufer-Shop',
    GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'pa_EAN' THEN t.name END) as 'EAN'
INTO OUTFILE '/var/log/mysql/export.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n'
FROM 
    wp_posts p
JOIN 
    wp_postmeta pm ON p.ID = pm.post_id
JOIN 
    wp_term_relationships tr ON p.ID = tr.object_id
JOIN 
    wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN 
    wp_terms t ON tt.term_id = t.term_id
WHERE 
    p.post_type = 'product'
GROUP BY 
    p.ID;

Adding additional postmeta values

If you wish to add additional postmeta values, you can duplicate the lines like this and adjust them for the key to pull the data from and the value to appear in our CSV output.

MAX(CASE WHEN pm.meta_key = '_wc_average_rating' THEN pm.meta_value END) as '_wc_average_rating',


Adding additional taxonomies

If you would like additional taxonomies, CSV formatted in their column when there are multiples, then you can duplicate these lines:

GROUP_CONCAT(DISTINCT CASE WHEN tt.taxonomy = 'product_tag' THEN t.name END) as 'product_tags',


Running your SQL

The easiest way to get this to generate output is to run this SQL from phpMyAdmin. In that case, just paste your code into phpMyAdmin and run it then click the export button.

However, you probably want to automate this SQL so here’s how to run this SQL manually from the command line first – knowing this will allow us to schedule the export.

1. Log onto your server using SSH or Putty
2. Save your SQL export script somewhere that cron will have access to
3. Alter the command below to include your username, password and database name and choose the output location and output file name then run it
4. Confirm the output file is created and is as desired

mysql --username="youruserfromwpconfig" --password="passwordfromwpconfig" dbname < /path/to/your/export.sql

Troubleshooting permissions

Your export folder should have permissions to write allocated to your mysql user. To find your mysql user run the following command:

ls -l /var/log/mysql/

Here we can see the user and group are both ‘mysql’.

So, if your output folder is /root/exports/ then allocate the owner and the group of that folder to mysql:

chown mysql:mysql /root/exports/

Automating your SQL export

Open up your crontab using:

crontab -e

Add a row to configure your export to run once weekly every Sunday. Alter this schedule how you wish:

0 2 * * 7 mysql --username="youruserfromwpconfig" --password="passwordfromwpconfig" dbname < /path/to/your/export.sql ; mv /var/log/mysql/export.csv /var/log/mysql/export_$(date +%Y%m%d).csv

You’ll notice there are 2 commands in the above cron – one to generate the csv file and one to rename it to have the current date. You can adjust this as you wish, but just note that MySQL will not overwrite a file if it already exists, so renaming it with the mv command is a good option.

Summary

Using the above, you can vastly reduce resources used to perform these weekly exports for Google Product XML feeds as well as save time on generating the export and guaranteeing that it will complete and the file will be updated every week. Exporting 1.8 million rows using the above SQL took 15 minutes in my client’s case.