There 50000+ attachments in my mantis database. While I tried to move attachments from db to disk by admin/system_utils.php, it show terrible performance which cost 2 seconds per attachment.
Finally, by digging mysql slow query log, I found that in core/file_api.php line 591(function diskfile_is_name_unique), the query sql took too much time. VIEW optimization in MySQL are still pretty primitive. Problem solved after I modified the code by put the WHERE statment into each UNION subquery as following:
$t_query = "SELECT count(*) FROM ( SELECT diskfile FROM $t_bug_file_table WHERE diskfile=" . db_param() ." UNION SELECT diskfile FROM $t_project_file_table WHERE diskfile=" . db_param() .") f";