Bug #58781 | ORDER BY use only first 10 chars on hoster side (Using filesort) | ||
---|---|---|---|
Submitted: | 7 Dec 2010 10:45 | Modified: | 7 Dec 2010 18:42 |
Reporter: | Sergey Tomashevsky | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Options | Severity: | S2 (Serious) |
Version: | 5.1.49-rel11.3 | OS: | Linux (Linux vh36.hoster.by 2.6.18-294.17.1.el5.lve0.7.40 #1 SMP Sat Oct 9 17:58:50 EEST 2010 x86_64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | Extra: Using filesort |
[7 Dec 2010 10:45]
Sergey Tomashevsky
[7 Dec 2010 11:51]
Peter Laursen
You have `product_name` varchar(64) DEFAULT NULL .. accordingly `product_name` is sorted as a string and not as a number. Looks perfect to me. Peter (not a MySQL person)
[7 Dec 2010 12:11]
Valeriy Kravchuk
Sorry, but I can not repeat this with current version, 5.1.53: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.53-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE IF NOT EXISTS `bak_vm_product` ( -> `product_id` int(11) NOT NULL AUTO_INCREMENT, -> `product_name` varchar(64) DEFAULT NULL, -> PRIMARY KEY (`product_id`), -> KEY `idx_product_name` (`product_name`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='All products are stored her e.' -> AUTO_INCREMENT=12 ; Query OK, 0 rows affected (0.22 sec) mysql> INSERT INTO `bak_vm_product` (`product_id`, `product_name`) VALUES -> (7, '12345678992'), -> (3, '1234567891'), -> (6, '12345678991'), -> (5, '1234567892'), -> (8, '12345678997'), -> (9, '12345678994'), -> (10, '1234567897'), -> (11, '1234567894'); Query OK, 8 rows affected (0.05 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> SELECT * -> FROM `bak_vm_product` -> ORDER BY `bak_vm_product`.`product_name` ASC -> ; +------------+--------------+ | product_id | product_name | +------------+--------------+ | 3 | 1234567891 | | 5 | 1234567892 | | 11 | 1234567894 | | 10 | 1234567897 | | 6 | 12345678991 | | 7 | 12345678992 | | 9 | 12345678994 | | 8 | 12345678997 | +------------+--------------+ 8 rows in set (0.00 sec) mysql> explain SELECT * -> FROM `bak_vm_product` -> ORDER BY `bak_vm_product`.`product_name` ASC; +----+-------------+----------------+------+---------------+------+---------+--- ---+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | re f | rows | Extra | +----+-------------+----------------+------+---------------+------+---------+--- ---+------+----------------+ | 1 | SIMPLE | bak_vm_product | ALL | NULL | NULL | NULL | NU LL | 8 | Using filesort | +----+-------------+----------------+------+---------------+------+---------+--- ---+------+----------------+ 1 row in set (0.02 sec) mysql> SELECT * -> FROM `bak_vm_product` -> ORDER BY `bak_vm_product`.`product_name` DESC; +------------+--------------+ | product_id | product_name | +------------+--------------+ | 8 | 12345678997 | | 9 | 12345678994 | | 7 | 12345678992 | | 6 | 12345678991 | | 10 | 1234567897 | | 11 | 1234567894 | | 5 | 1234567892 | | 3 | 1234567891 | +------------+--------------+ 8 rows in set (0.00 sec) Please, ask your hoster to upgrade...
[7 Dec 2010 13:07]
MySQL Verification Team
maybe hoster has max_length_for_sort_data=10 in my.cnf ?
[7 Dec 2010 16:48]
Sergey Tomashevsky
Yes, it's hoster's limitation. I asked, if they could increase till 100. They answered: Unfortunately, no. Such restrictions have listed in the Appendix № 1 to the public contract. :(