ORDER BY causing disk space error

cesarcesar

The following query is causing a mysql error The Error number is 28, which means low disk space. So I clear some space, retry and get the error again after a few times querying. After playing with query i find that the ORDER BY statement is what make it fail. If i remove it the script runs.

So my question is, is there something about an ORDER BY statement that takes up a lot of disk space? I can sort in PHP, but if MySQL has the ability then i prefer it to sort with.

Thanks for all the suggestions.

SELECT data.datasize AS size, data.dataid AS id, data.datatype, data.dataname, data.datadescription AS description, data.engineerid AS owner, datalog.usability AS usability,datalog.easeofdownload AS easeofdownload, datalog.docaccuracy AS accuracy, datalog.timesdownloaded AS timesd, datalog.ratings AS ratings from data, datalog where data.datatype = 'AE Training video' AND data.dataid = datalog.dataid ORDER BY data.dataname

nemonoman

When you ORDER by a non-indexed column, MySQL creates a temporary table to do the ordering. You are collecting a lot of columns -- if you also have a lot of rows being returned, the temporary table could be rather large.

Also if you have not indexed data.dataid AND datalog.dataid then temporary tables will also be used to create the Join

You can isolate the problem this way:
SELECT data.dataname from data, datalog where data.datatype = 'AE Training video' AND data.dataid = datalog.dataid ORDER BY data.dataname

If this gives a disk space error, then you can assume that the JOIN is eating up the diskspace.

If this DOESN'T give a diskspace error, then you can assume it is the VOLUME OF DATA being returned that is causing your problem.

You could also look at the EXPLAIN:
EXPLAIN SELECT data.dataname from data, datalog where data.datatype = 'AE Training video' AND data.dataid = datalog.dataid ORDER BY data.dataname

If you don't understand the EXPLAIN output, you can post it and one of the SQL gurus will help interpret.

cesarcesar

thank much. I will implement your suggestions an report back.