Skip to main content
Database Administrators

Return to Answer

Updated based on comment
Source Link
David I.
David I.
  • 173
  • 1
  • 6

It looks like the database was trying to updateselect 30593 rows, and maybe it was taking a long time. Since no keys are identified in the 'explain', make user uuser_id and file_id have indexes on them. Postingadd an index to file_name_eng since it is in the order clause.

As RolandoMySQLDBA suggested, carefully evaluate if you really need a select * there. Add LIMIT clauses and paging of results if you are showing this to a user somewhere (and not just dumping all records to a file)

Also be aware of "show createMyISAM table files"locks. Long running selects will helpblock updates as RolandoMySQLDBA said. I don't think you have to convert to InnoDB as a first step though. Large MyISAM tables work OK if you are careful to use indexes and don't do "SELECT *" queries without a LIMIT, or a WHERE clause at least.

It looks like the database was trying to update 30593 rows, and maybe it was taking a long time. Since no keys are identified in the 'explain', make user uuser_id and file_id have indexes on them. Posting the results of "show create table files" will help.

It looks like the database was trying to select 30593 rows, it was taking a long time. Since no keys are identified in the 'explain', add an index to file_name_eng since it is in the order clause.

As RolandoMySQLDBA suggested, carefully evaluate if you really need a select * there. Add LIMIT clauses and paging of results if you are showing this to a user somewhere (and not just dumping all records to a file)

Also be aware of MyISAM table locks. Long running selects will block updates as RolandoMySQLDBA said. I don't think you have to convert to InnoDB as a first step though. Large MyISAM tables work OK if you are careful to use indexes and don't do "SELECT *" queries without a LIMIT, or a WHERE clause at least.

Source Link
David I.
David I.
  • 173
  • 1
  • 6

It looks like the database was trying to update 30593 rows, and maybe it was taking a long time. Since no keys are identified in the 'explain', make user uuser_id and file_id have indexes on them. Posting the results of "show create table files" will help.