0

Background on relevant character sets

MySQL and MariaDB support the deprecated utf8mb3 character set and collations. (utf8 is currently an alias for utf8mb3.) This supports all Unicode code points on the Basic Multilingual Plane (BMP) and stores on-disk using up to 3 bytes.

The more inclusive utf8mb4 character set supports all Unicode code points (including BMP) using up to 4 bytes on-disk. (utf8 will alias to utf8mb4 in the future.) Everything storable with utf8mb3 is stored the same way (bit-for-bit) in utf8mb4. Some things storable with utf8mb4 are not storable with utf8mb3.

Our tables

We have an index definition using the new character set:

user_email VARCHAR(50) CHARACTER SET utfmb4 UNIQUE

and somewhere else have one using the deprecated version

email_in_another_table VARCHAR(50) CHARACTER SET utf8 UNIQUE

The question

Will a query using the two indices avoid reading row data (which is slower than indices)?

SELECT user_email
  FROM a
 UNION -- this is an intersection
SELECT email_in_another_table
  FROM b
Improve this question
3
  • Hi there. Have you tried it? This seems like it would be fairly easy to test. Are you able to execute such a query and view the execution plan / do an "explain"? With utf8mb4 being newer, it might use collation rules from a newer version of Unicode, which wouldn't be compatible with older rules.
    – Solomon Rutzky
    Dec 30, 2019 at 17:14
  • 1
    There is something wrong on the question. You say "UNION -- this is an intersection", that is just a union that will not print duplicate rows.You can emulate an intersection with an inner join, is that what your really want to ask, if JOIN ... ON user_email = email_in_another_table will efficiently use the index?
    – jynus
    Dec 31, 2019 at 12:17
  • 1
    My impression is that it treats utf8mb3 and utf8mb4 as different character sets, hence one must be converted to the other before considering comparing. So, not efficient.
    – Rick James
    Jan 4, 2020 at 5:36

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.