Here are a few useful tips to assist in determining the impact of your queries on your database.
If you have multiple databases that are load balanced or want to ensure that you are on the intended database server/instance:
SELECT @@hostname;
To check what queries are currently running:
SHOW FULL PROCESSLIST;
Often when checking your query performance using EXPLAIN, it tells you the possible keys/indexes that will be used. But what can be even more useful to determine just how many rows are being scanned each time it runs, embed your query within the following:
FLUSH STATUS;
SHOW SESSION STATUS LIKE "Handler%";
SELECT * FROM `mytable`;
SHOW SESSION STATUS LIKE "Handler%";
The Value for Variable_name = Handler_read_next is the number of records actually scanned during your query.
Variable_name Value
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 2
Handler_read_next 806
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 0