MySQL uses only the first 20 characters of a string when ordering records

Question

It seems that when Im uses the order by name operator, where name is of type varchar(255) , MySQL on my server does not put the records in the correct order if they have the same 20 first characters of the name field. MySQL doesn't seem to care about the 21st character at all: it actually keeps the same wrong order when sorting in descending order.

I repeated my table on another MySQL installation, and everything is fine there. But what should I do with this restriction on the server? I cannot reinstall MySQL there because I use shared hosting.

Update : the name field does not belong to any index, and creating an index in this field also does not help.

MySQL version is 5.1.55, the engine is MyISAM.

Update 2 . I originally used cp1251_general_ci sort, but then I tried other sortings and got the same result. For strings, I used '123456789012345678901'/'123456789012345678902' and 'abcdefghijklmnopqrstauvwxyz'/'abcdefghijklmnopqrstbuvwxyz' , the same result.

The order, apparently, does not take into account all the characters starting from the 21st, but otherwise it works as it should.

Interestingly, when using ORDER BY substring ( name , 2) , the 21st character is significant, and the 22nd is not.

Answer 1

can you check the max_length_for_sort_data and max_sort_length variables? the default is 1024, if you have one of those set to 20, then this all explains.

 mysqladmin -u root -p variables | grep sort Enter password: | max_length_for_sort_data | 1024 | | max_sort_length | 1024 | | myisam_max_sort_file_size | 2146435072 | | myisam_sort_buffer_size | 8388608 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on | | sort_buffer_size | 2097144 | 

You can find additional information on the ORDER BY optimization chapter of the mysql server manual and max_length_sort_data .