Mysql – BigInt or Varchar for a large number I won’t ever sort / calculate on

You will need to check out the sum total of your indexes for MyISAM. Please run this query:

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) 
recommended_key_buffer_size FROM 
(SELECT LEAST(POWER(2,32),KBS1) KBS 
FROM (SELECT SUM(index_length) KBS1 
FROM information_schema.tables 
WHERE engine='MyISAM' AND 
table_schema NOT IN ('information_schema','mysql')) AA ) A, 
(SELECT 2 PowerOf1024) B; 

Whatever number comes out, use either that number or 8G, whichever is smaller.

MyISAM only caches index pages.

If you plan to migrate your data to InnoDB, use these settings:

[mysqld]
innodb_file_per_table
innodb_log_file_size=2047M
innodb_log_buffer_size=64M
innodb_buffer_pool_size=18G
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=10000

You wiil need to optimize the InnoDB infrastructure

Two possibilities: Either pad your values with zeros from the left and sort based on that:

SELECT _tid, _name FROM teacher ORDER BY lpad(_tid, 3, '0');
-- choose an appropriate number instead of 3

or cast your values to a number (similar to Aaron W's solution, apart from here I cast explicitly, and that is a clearer solution):

SELECT _tid,_name FROM teacher ORDER BY cast(_tid as decimal);

Be careful if you have non-number strings in your field:

SELECT cast('something' as decimal);
0
SELECT 'something' + 0;
0