New answers tagged mysql-5
When referring to ib* files, are you also referring to the ib_log files? They also need to be removed when you remove the ibdata* files. Have a look at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql that might be of some help.
Yes, the large offset is a problem. MySQL does early row lookups, which do not allow throwing data away as easy as one imagines. MySQL retrieves the whole records as it processes the database and each record may be of variable length. Throwing rows away afterwards becomes as cumbersome as throwing each single row away. It's not like "throw away x*y bytes". ...
You could look at this document about account resource limits?
It's probably because the reverse scanning of an index can be slower that forward scanning, as explained, for example, here: http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan/
Unfortunately, prior to 5.5, performance_schema isn't available and that might have been able to solve your problem - of interest are Mark Leith's ps_helper and Jesper Wisborg's ps_tools Both are actually current Oracle/MySQL employees. Upgrade? Failing that, what about OS throttling? If you use Linux or similar, you should be able to restrict resources on ...
Top 50 recent answers are included