SELECT * FROM TABELA Order by CAST(ColunaVARchar as SIGNED );
Está escrito na Documentação do MySQL
CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)
The CAST() and CONVERT() functions take a value of one type and produce a value of another type.
The type can be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
BINARY produces a string with the BINARY data type. See The BINARY and VARBINARY Types for a description of how this affects comparisons. If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument. As of MySQL 5.0.17, values shorter than N bytes are padded with 0x00 bytes to a length of N.
CHAR(N) causes the cast to use no more than N characters of the argument.
The DECIMAL type is available as of MySQL 5.0.8.
CAST() and CONVERT(… USING …) are standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax.
CONVERT() with USING is used to convert data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string ‘abc’ in the default character set to the corresponding string in the utf8 character set
Hum… é por isso que os sistemas acabam ficando lentos.
Não dá para o otimizador do SQL usar algum índice aqui para processar sua consulta, então ele tem de pegar tudo e ordenar na mão, o que costuma ser bem mais lento.
Se o campo já fosse numérico logo de cara, não haveria esse problema.