Why database collations matter for import speed

A collation is the set of rules your database uses to compare and sort text: whether Café equals cafe, whether two different emoji count as the same character, and so on. Every text column in your WordPress database has one, and two columns can have different collations even when they sit side by side in the same table.

Super Speedy Imports compares text all the time during an import: your file’s SKUs with the SKUs already in WordPress, your category paths with the categories that exist, your product attributes with WooCommerce’s attribute list. How fast those comparisons run depends on whether the two sides use the same collation.

Matching collations keep comparisons fast

The database finds matching rows quickly by using an index, a sorted copy of a column’s values that it can search instead of reading every row. An index is sorted by its column’s collation, so it can only answer a comparison made under that same collation.

• Same collation on both sides: the database compares the values directly and uses the index.
• Different collations: the database first converts one side to the other side’s collation,

row by row, and the converted side can no longer use its index. The comparison still gives the right answer; it just does more work to get there.

On a small import the difference is hard to notice. On a large catalogue, where one comparison can cover hundreds of thousands of rows, it is the difference between a quick indexed lookup and a conversion of every row.

What Super Speedy Imports matches by itself

Every working table Super Speedy Imports creates for an import takes the collation of the WordPress column it is compared with, read column by column from your database: its copy of your SKUs matches wp_postmeta.meta_value, its category names match wp_terms.name, its taxonomy names match wp_term_taxonomy.taxonomy, and so on. Those comparisons are always direct, whatever collations your WordPress tables use.

Two kinds of comparison cannot be matched that way, because neither side is a table the plugin creates:

1. A WordPress column compared with another WordPress column. The WooCommerce attribute lookup

matches each variation’s attribute value in wp_postmeta.meta_value with a term slug in wp_terms.slug. If those two tables use different collations, the database has to convert the wp_postmeta side while that step runs.

1. Different character sets. A character set is the encoding behind a collation. If, for example,

wp_terms uses utf8 (also called utf8mb3) while wp_posts uses utf8mb4, a working table can only take one of them, so comparisons with the other one are converted.

The Preview & Run warning

When an import meets either kind, Preview & Run shows “Some of your database columns use different collations”. It names each pair of columns, their collations and the step where they are compared. Your import runs and your data imports correctly; those comparisons are slower than they would be on matching tables. Tick the box to run the import anyway.

wp ssi <id> prints the same list and continues, and a scheduled import records it on the run.

Fixing mismatched collations

Aligning your tables makes every one of those comparisons direct again, which speeds up the steps the warning names on every future import. It is also how WordPress expects its tables to be: a fresh install creates them all with one character set and collation.

The warning includes the SQL that converts the mismatched tables to what WordPress itself uses for new tables on your site, usually utf8mb4 / utf8mb4_unicode_520_ci. Copy SQL copies it, and Download .sql saves it as a file you can run yourself or send to your host. It looks like this:

ALTER TABLE `wp_posts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE `wp_postmeta` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

It lists every table among those the plugin compares (wp_posts, wp_postmeta, wp_terms, wp_term_taxonomy and wp_termmeta) whose text columns differ from that target, not only the two tables of each warned comparison. Converting only one side of a pair can leave it different from a third table it is also compared with.

Before you run it:

1. Take a full database backup. CONVERT TO rewrites every row of each table.
2. Pick a quiet time. CONVERT TO rebuilds each table and locks it while it does so. On a large

wp_postmeta that can take minutes, and nothing can be written to the table meanwhile.

1. Run it in phpMyAdmin (select the database, open the SQL tab, paste, Go), with

wp db query < super-speedy-imports-collation-fix.sql over SSH, or send the file to your host.

Each statement converts one table on its own. If one fails, the tables before it are converted and that table is left as it was; contact support with the message.

Checking your collations

This query lists every text column of the tables the plugin compares, with its character set and collation. Run it in phpMyAdmin or with wp db query, changing wp_ if your table prefix differs.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME IN ('wp_posts', 'wp_postmeta', 'wp_terms', 'wp_term_taxonomy', 'wp_termmeta')
   AND COLLATION_NAME IS NOT NULL
 ORDER BY TABLE_NAME, COLUMN_NAME;

When every row shows the same character set and collation, every comparison the plugin makes is direct and the warning does not appear.

Questions

How do tables end up with different collations? Usually through the site’s history. Sites installed before WordPress 4.6, or converted by WordPress’s own utf8mb4 upgrade, have utf8mb4_unicode_ci tables, while tables created later, including WooCommerce’s, get utf8mb4_unicode_520_ci on servers that support it. Tables converted one at a time by hand or by a migration tool can also differ.

Will converting change anything on my site? Text comparisons follow the new collation. Between utf8mb4_unicode_ci and utf8mb4_unicode_520_ci the differences are small (the 520 rules are a newer version of the same Unicode standard): for example, two different emoji compare as equal under utf8mb4_unicode_ci and as different under utf8mb4_unicode_520_ci, so a search or a sort can treat a handful of characters differently afterwards.

Do I need to change DB_COLLATE in wp-config.php? No. DB_COLLATE only affects tables created after it is set. Converting the existing tables is what aligns them.

Why does the plugin not convert the tables itself? Converting locks large tables, changes how WordPress compares its own data, and cannot be undone without a backup. That decision belongs to the site owner, with a backup in hand, rather than to an import running in the background.

Further reading

Related products

Super Speedy ImportsEasy to use. Built for speed.

Written for Super Speedy Imports 3.63.0.