3

Many tables will do fine using CHARACTER SET ascii COLLATE ascii_bin which will be slightly faster. Here's an example:

CREATE TABLE `session` (
    `id` CHAR(64) NOT NULL,
    `created_at` INTEGER NOT NULL,
    `modified_at` INTEGER NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) CHARACTER SET ascii COLLATE ascii_bin;

But if I were to join it with:

CREATE TABLE `session_value` (
    `session_id` CHAR(64) NOT NULL,
    `key` VARCHAR(64) NOT NULL,
    `value` TEXT,
    PRIMARY KEY (`session_id`, `key`),
    CONSTRAINT FOREIGN KEY (`session_id`) REFERENCES `session`(`id`) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

what's gonna happen? Logic tells me it should be seamless, because ASCII is a subset of UTF-8. Human nature tells me I can expect anything from a core dump to a message Follow the white rabbit. appearing on my screen. ¯\_(ツ)_/¯

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Terax
  • 130
  • 5
  • It is OK to have different columns in the _same_ table be different `CHARACTER SET` and/or `COLLATION`. Whenever you have the 'same' value in two different tables, make those columns the _same_; don't depend on 'subset' rules. – Rick James Nov 03 '18 at 03:05

2 Answers2

1

Does joining ASCII and UTF-8 tables add overhead?

Yes.

If you do 

SELECT whatever 
  FROM session s
  JOIN session_value v 
         ON s.id = v.session_id

the query engine must compare many values of id and session_id to satisfy your query.

If id and session_id have exactly the same datatype, the query planner will be able to exploit indexes and fast comparisons.

But if they have different character sets, the query planner must interpret your query as follows.

 ...  JOIN session_value v 
         ON CONVERT(s.id USING utf8mb4) = v.session_id

When a WHERE or ON condition has the form f(column) it makes the query non-sargable: it prevents efficient index use. That can hammer query performance.

In your case, similar performance problems will occur when you insert rows to session_value: the server must do the conversion to check your foreign key constraint.

If these tables are going to production, you'd be very wise to use the same character set for these columns. It's much easier to fix this when you have thousands of rows than when you have millions. Seriously.

What makes a SQL statement sargable?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

Why not UTF-8 all the way through? Having ASCII tables is usually a mistake, a sign you forgot to set the encoding on something. Using a singular encoding vastly simplifies your internal architecture.

Encoding is only relevant if and when you have CHAR, VARCHAR or TEXT columns.

If you have a column of that type then it's worth setting it as UTF8MB4 by default.

tadman
  • 208,517
  • 23
  • 234
  • 262