If a query has frozen up the MySQL server, you can see the troublesome query by running the following command (works in phpMyAdmin too)
you can also see the cache and allocation of the server by the following command:
SHOW VARIABLES LIKE 'query_cache%';
The following options may assist in resolving these issues:
- Adding indexes to the fields which are being sorted.
- Optimising the query to remove unneccesary joins on large fields
- If lots of joins, consider creating a temporary table that has all the info you need
- Changing your storage engine from MyISAM to InnoDB (source of this info here). This will assist with fixing table-level locks, as InnoDB only locks at a row level
- Engines are defined at the TABLE level – To find out what engine your tables are using, run this command:
SHOW TABLE STATUS FROM hpry_tas_prod
- To change the type of engine a table uses, (in phpMyAdmin) just go into the properties of the table, and click on the Operation tab. You can change the engine in there.