MySQL using only first 20 characters of a string when ordering records

mysql, sql-order-by

Solution

could you check your max_length_for_sort_data and max_sort_length variables? default is 1024, if you have one of those set to 20 then that explains it all.

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 more info ORDER BY optimisation chapter of the mysql server manual and at the max_length_sort_data definition.

Problem

It appears that when I’m using `order by name` statement where `name` has a `varchar(255)` type, MySQL at my server doesn’t place records in proper order if they have same 20 first characters of `name` field. It seems like MySQL doesn’t care about 21st character at all: it actually preserves the same incorrect order when sorting in descending order. I replicated my table on another MySQL installation and everything is OK there. But what do I do about this limitation on a server? I can’t reinstall MySQL there because I’m using shared hosting. Update: the `name` field doesn’t belong to any index and creating index on this field doesn’t help either. MySQL version is 5.1.55, engine is MyISAM. Update 2: I originally used `cp1251_general_ci` collation but then I tried other collations and got the exact same result. For strings I used `'123456789012345678901'/'123456789012345678902'` and `'abcdefghijklmnopqrstauvwxyz'/'abcdefghijklmnopqrstbuvwxyz'`, same result. Ordering seems to not take into consideration all characters starting from the 21st, but otherwise it’s working as it should. Interestingly, when using `ORDER BY substring(`name`, 2)` the 21st character matters but the 22nd does not.

Original source