If a query has frozen up the MySQL server, you can see the troublesome query by running the following command (works in phpMyAdmin too)
show processlist;
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.