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.