0

I have some rather large CSV files that I am loading into my MySQL 5.7 database. The files are several gigabytes in size, several million lines long, and have large column widths that must be used in joins (sometimes as long as ~500 characters).

The data is all standard English characters, and most of the columns can fit into a single byte character set like latin1. However, several of the columns require unicode for things like trademark/registered/copyright symbols, measurement symbols (inches, feet, radius, etc), and therefore I've been using utf8mb4 on all tables.

The problem with doing this is twofold. It blows up our index sizes, so in some cases, we can't create an index on a column(s) because the width becomes greater than 3072. Additionally, it seems to be having a significant performance impact, presumably because the data size is 4x.

What I'd like to do is use latin1 on all columns in the table, and only utf8mb4 on columns that need it. This leads to my questions -

What's the best way to identify for sure which columns are actually storing multibyte characters? Can I detect that somehow, either within my CSV prior to loading (using python/pandas maybe?), or from within the database? The files are stored as utf8. They are currently loaded into a utf8mb4 table. If I could easily scan the table and say "this column contains no multibyte data", I could change it to latin1.

Second, will I run into problems if I try to create composite indexes with columns using different encodings? Say column A is utf8mb4 and column B is latin1. Is there anything wrong with creating an index on these two columns? ie: CREATE INDEX my_index ON my_table(A, B);. I'm assuming there's no issue doing that.

Improve this question
2
  • A little more googling turned up this: robpeck.com/2016/08/… - SELECT id FROM posts WHERE body <> CONVERT(body USING ASCII)
    – blindsnowmobile
    May 13, 2019 at 22:11
  • 1
    second part, correct, there's no issue with compound indexes of different encodings, orderings or even types (note: is very simple to test).
    – danblack
    May 14, 2019 at 1:17

1 Answer 1

Reset to default
1

The data size is not 4x. English text, even in utf8mb4, takes only one byte per character. Trademark(etc) symbols are multi-byte. Then ones you mentioned are only 2 bytes. Emoji and some Chinese is where 4 bytes become necessary.

Don't create indexes on large columns. Don't create indexes until you have the queries -- derive the optimal indexes from the queries.

It is perfectly fine (in MySQL, at least) to have one column be latin1 and another be utf8mb4 (etc). And both of those can be in the same index.

I suggest making a couple of passes over the data. First bring everything in with utf8mb4, no indexes, wide columns (eg TEXT). Then analyze what you got -- SELECT MAX(CHAR_LENGTH(col2)), ...; test for non-latin1, etc. For the second pass, re-do the schema to be closer to max len, etc.

Improve this answer
4
  • Thanks. Are there any performance considerations to keep in mind when using utf8mb4 vs latin1? Is it ever worthwhile to keep a table latin1, except for a few, select columns that need multibyte characters? I have multiple tables that join together on either a) long product name columns, ie: varchar(512), or b) alphanumeric codes, ie: varchar(30). At least for the alphanumeric codes, I don't need utf8. For the long product names, I don't need emojis or Chinese, so maybe it makes sense to use utf8 over utf8mb4. Note: I don't control the source data.
    – blindsnowmobile
    May 14, 2019 at 18:30
  • The major performance concern is how many rows need to be looked at. Character Sets is at most a secondary concern.
    – Rick James
    May 15, 2019 at 0:46
  • I did some testing yesterday, and didn't see any performance benefits with my data. Additionally, keeping track of which columns are using which encoding, across multiple tables, in order to avoid a CONVERT() on the join, adds additional complexity which makes the mixing/matching less attractive.
    – blindsnowmobile
    May 15, 2019 at 18:03
  • @blindsnowmobile - Yes, do avoid the need for conversion (explicit or implicit) in a JOIN, it could prevent the use of an INDEX, leading to a table scan, hence poor performance. If necessary, make most columns utf8mb4.
    – Rick James
    May 15, 2019 at 18:53

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.