CODEWITHSUNDEEP
mysqlcollation
user1432124
user1432124

Reputation:

UTF8_general_ci collation taking a couple of seconds to process

I have made a table in collation utf8_general_ci .

CREATE TABLE `general` (
`one` varchar( 250 ) NOT NULL ,
`two` varchar( 250 ) NOT NULL ,
`time` varchar( 250 ) NOT NULL,);

and entered 1000 records in it.

insert into general values('ankit','hamitte','13364783'),('john','will','13364783');

Now when i am selecting the records and sorting them then it takes a couple of second while when i use armscii_general_c1 it loads instantly.What is the main reason for this and which collation should be used by me

Upvotes: 0

Views: 103

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53880

I'm guessing you have all three columns in a multi-column index?

If so, then with utf8, they won't fit. MySQL will have to scan rows. A MySQL index is limited to 767 bytes in InnoDB (1000 for MyISAM), and indexes are fixed width, so UTF8 indexes are three times the size of single byte encoded indexes.

You'll only fit one column into the index with UTF8.

So, with the single-byte encoding, MySQL can utilize the index fully, whereas with the multi-byte encoding, MySQL cannot fully utilize the index.

Upvotes: 1

Index
Index

Reputation: 746

Take in mind that UTF8 requires 2 times more space to hold your UTF8 chars. 1 UTF8 char will be stored as 2 chars describing it. As a result it will take twice more time to make comparison of UTF8 strings. Statement above does not relate to latin1 symbols as they will be stored as usual.

Please post full create table statement. As I may assume that problem is in indexes. Just run show create table general and post here output.

BTW: sorting is very time consuming operation for DB, so you have to use indexes to make it fast.

Upvotes: 0

Related Questions