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

Yeah, order by means sort, which means resequencing data after it's gathered. This forces your server to allocate temp space so as not to have to do it in memory. You may be able to use a query hint to force a different execution plan, but if the result set is large enough, your hint probably won't be honored anyway.

This is an example of why people should switch away from MySQL.

Not related to the original question, but try switching to ANSI-92 query style and use table aliases to save yourself some headache.

And don't alias data on output with the same name as the column name... that's redundant. Personally, I don't recommend aliasing any column names on output. It gets confusing when working with large databases.

SELECT 
    d.datasize AS size, 
    d.dataid AS id, 
    d.datatype, 
    d.dataname, 
    d.datadescription AS description, 
    d.engineerid AS owner, 
    dl.usability, 
    dl.easeofdownload, 
    dl.docaccuracy AS accuracy, 
    dl.timesdownloaded AS timesd, 
    dl.ratings
FROM data AS d
    INNER JOIN datalog AS dl ON d.dataid = dl.dataid
WHERE d.datatype = 'AE Training video'
ORDER BY d.dataname

Do you have an index created on dataname field, if not then
create an index on dataname field and try again.

Also can you show what does 'explain select ...' tell you about this query?