StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCOLLATE in SQL statements on fields in utf8_bin slower than using the default collation?
text
Body
copied!<p>Two scenarios:</p> <p>Using the default collation:</p> <pre><code>CREATE TABLE IF NOT EXISTS `table` ( `name` varchar(255) collate utf8_general_ci NOT NULL, UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; SELECT `name` FROM `table` ORDER BY `name`; </code></pre> <p>Using <code>COLLATE</code>:</p> <pre><code>CREATE TABLE IF NOT EXISTS `table` ( `name` varchar(255) collate utf8_bin NOT NULL, UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; SELECT `name` FROM `table` ORDER BY `name` COLLATE utf8_general_ci; </code></pre> <p>I need to change from the first scenario to second because the index is case insensitive. Still ordering is important. There are experimental collations as <code>utf8_general_cs</code> but it requires special compilation.</p> <h1>Will this have an impact on the performance?</h1> <p>In my opinion if <code>MySQL</code> stores text fields internally in utf8 independent of collation it should not affect performance.</p> <p><strong>Edit:</strong> The output of explain in case <code>COLLATE</code> is used is the same as without.</p> <pre><code>mysql> EXPLAIN SELECT * -> FROM `table` -> ORDER BY `name` -> COLLATE utf8_general_ci; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * -> FROM `table` -> ORDER BY `name`; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload