MySQL error 126 when adding ORDER BY -

i'm trying figure out issue i've been having. have around ~1m records in table, , ~10 joins.

i'm not going post query, don't think it's part of problem. basically, without order m0_.id clause, query runs , returns results in 0.0056 seconds. isn't bad. moment add order by, query fails following error:

#126 - incorrect key file table '/tmp/#sql_527_0.myi'; try repair it

so i've been reading around, , apparently of time, issue has space. can't (can it?), because i've got 30gb free , entire database 200mb. went further, , found sort_buffer_size option. ran set global sort_buffer_size = 18446744073709551615, apparently maximum platform. sure enough, running show variables 'sort_buffer%'; returns

+------------------+----------------------+ | variable_name    | value                | +------------------+----------------------+ | sort_buffer_size | 18446744073709551615 | +------------------+----------------------+ 1 row in set (0.00 sec) 

i tried limiting query 1 result, no dice.

any ideas?

i think you'd better create clone of table , rename them:

1. get create table query:

   show create table table_name;

2. run query (1) step change table_name table_name_new

3. copy data origin new:

   insert table_name_new values (select * table_name);

4. try select table_name_new

or try create view of heavy query without adding order by:

create view view_heavy_query here goes heavy query joins; 

and query like:

select * view_heavy_query order field;