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.
SELECT id FROM posts WHERE body <> CONVERT(body USING ASCII)