Differences

This shows you the differences between two versions of the page.

--- mysql:performance_using_case_insensitive_columns [2016/07/03 10:50] – created peter
+++ mysql:performance_using_case_insensitive_columns [2020/07/15 09:30] (current) – external edit 127.0.0.1
@@ Line 8: / Line 8: @@
 <code mysql>
-SELECT BENCHMARK(500000000, _utf8mb4'foo' < _utf8mb4'zot' COLLATE utf8mb4_bin);
+SELECT BENCHMARK(500000000, _utf8mb4'foo' < _utf8mb4'blah' COLLATE utf8mb4_bin);
 </code>
@@ Line 14: / Line 14: @@
 <code mysql>
-row in set (5.88 sec)
+row in set (7.82 sec)
 </code>
@@ Line 21: / Line 21: @@
 <code mysql>
-SELECT BENCHMARK(500000000, _utf8mb4'foo' < _utf8mb4'zot' COLLATE utf8mb4_unicode_ci);
+SELECT BENCHMARK(500000000, _utf8mb4'foo' < _utf8mb4'blah' COLLATE utf8mb4_unicode_ci);
 </code>
@@ Line 28: / Line 28: @@
 <code mysql>
-row in set (16.27 sec)
+row in set (21.25 sec)
 </code>
-As you can see, though, case sensitive was nearly three (2.77) times faster than case insensitive.
+As you can see, though, case sensitive was nearly three times faster than case insensitive.
-My suggestion to you is that unless you need the column to be case insensitive for comparison or search, you should certainly consider using the case insensitive character set, for example **utf8_generic_ci** would be **utf8_bin** and **utf8mb4_generic_ci** would be **utf8mb4_bin**.
+Therefore it is recommended to use a case-sensitive character set wherever possible.
-For CHAR and VARCHAR other character sets also have case insensitive versions, and if not you can alter the column to be VARCHAR BINARY:
+That is to use **utf8mb4_bin** instead of **utf8mb4_generic_ci**.
+It would be good to alter the column to be VARCHAR BINARY:
 <code mysql>
 ALTER TABLE `mytable1` CHANGE `mycolumn` `mycolumn` VARCHAR(254) BINARY NOT NULL;
 </code>
-However, I don't think that's ideal, instead use VARBINARY, and in fact here's a little chart for which to change to:
+However. if you do need to use a case-insensitive character set, then consider that CHAR and VARCHAR also have case insensitive versions.
+Here's a little chart for which to change to:
 |CHAR| 	BINARY|