Com_XXXX vs Handler_XXX

MySQL & MariaDB Other MySQL® Questions
1

Hello everybody.

I would like to know the differences between Com_XXX and Handler_XXX status variables.I tried some thing which give strange results and I really don’t understand why. For exemple, I thought that Com_XXX count every requests, bad, good, results found into cache or not and Handler_XXX only queries that goes into the engine.BUT :
±---------------±------+
| Variable_name | Value |
±---------------±------+
| Com_delete | 1909 |
| Handler_delete | 2175 |
±---------------±------+

Why Com_delete is smaller??? (it from MySQL 4.0)

On a mysql 5.0.45 :
mysql> SHOW STATUS LIKE ‘%_delete’;
±---------------±------+
| Variable_name | Value |
±---------------±------+
| Com_delete | 0 |
| Handler_delete | 0 |
±---------------±------+
2 rows in set (0.00 sec)

mysql> DELETE FROM Country;
mysql> SHOW STATUS LIKE ‘%_delete’;
±---------------±------+
| Variable_name | Value |
±---------------±------+
| Com_delete | 2 |
| Handler_delete | 0 |
±---------------±------+

Why my DELETE has not been through the engine (MyISAM in that case)?

2

Maybe because you are deleting all the rows it optimised it internally into a “truncate table” or similar implementation?

3

not really, the delete was on a part of the table and was the first sql query.

Moreover, the delete should normally always go through the datafile. I don’t see how it can delete row only working in memory.