Delete Annotations Records Using MySQL Command

Summary of my website: Elgg Version: 3.3.8, MySQL Version: 8, PHP Version: 7.

I wanted to delete an entire subtype from my database. But entities for that subtype have millions of annotations associated with them. I currently use below approach to delete the entities BUT it uses like 200+% CPU usage and takes very long time.

$options = [

     'type' => 'object',

     'subtype' => 'subtype_I_want_to_delete'


$entities = elgg_get_entities($options);

foreach ($entities as $entity) {

   if ($entity instanceof ElggObject) {

      $entity->delete( );



Is it safe to manually delete records associated with entities of targeted subtype in annotations table? Later, I would use the above approach to remove those entities.

Thanks in advance for your help!

  • Use ElggBatch instead. The documentation is very good at explaining how to do it

  • Thanks RvR for a quick response! I will try to use ElggBatch as suggested.

  • It's not recommended to manually remove data from the database unless you're VERY sure you know what you're doing.

    The suggestion to use ElggBatch will help with out of memory issues. Also don't forget to set 'batch_inc_offset' to false

    Another tip is to make a simple php script and execute it from the commandline (make sure you have some output to make sure the script is still running) this will remove the overhead of a webserver and make the script run faster.

  • Thanks Jerome!

    I tried ElggBatch without the 'batch_inc_offset' and the deletion process is still consuming 200+% CPU usage.  I will try to set the batch_inc_offset to false as you suggested.

    I delete entities of this subtype using simple command line below to call my cronjob. 

    while true


              curl https://mysite/cron/delete_entities_interval?force=1


    and for the deletion part. Below is the code block I've been using to delete one entity at a time:

    $options = ['type' => 'object', 'subtype' => 'subtype_needed_to_be_deleted', 'limit' => 1];

    $entities = new ElggBatch('elgg_get_entities', $options);

    foreach ($entities as $entity) {...deletion process---}


    With above approach, it is still slow. I also increased the innodb_buffer_pool_size and innodb_buffer_pool_chunk_size to 4GB. That didn't help neither.

    I notice one thing that when deleting entities from the database, it returns many rows like 30+ rows when running 'show processlist;' in the mysql command prompt. This website is on development and nobody has access to it. So I thought the 'show processlist' should return only a couple rows - not 30+ rows.

    Thanks again Jerome!

  • Changing to ElggBatch will not make the process consume less CPU it'll go as fast as it can thus using all CPU.

    If you want it to slow down build in a sleep(). Bu then it'll take longer.

    Also with ElggBatch make sure 'limit' is set to false.

    Also if your calling your cron job with curl you'll get the overhead of the webserver. Just make a script which does this part

    // start Elgg (in pseudo code)
    - Load the Composer autoloader
    - start the Elgg Application
    // start removing your entities
    $options = [
        'type' => 'object', 
        'subtype' => 'subtype_needed_to_be_deleted', 
        'limit' => false,
        'batch' => true,
        'batch_inc_offset' => false,
    $entities = elgg_get_entities($options);
    foreach ($entities as $entity) {
       echo "Removing GUID: {$entity->guid}" . PHP_EOL;
       ...deletion process---

    The foreach loop will go on until all entities have been removed

  • Thanks Jerome! I will try your recommendation and let you know how it goes.

  • With your approach, the CPU usage is mostly below 50% for MySQL service. Awesome!!

    Thanks again Jerome!

Beginning Developers

Beginning Developers

This space is for newcomers, who wish to build a new plugin or to customize an existing one to their liking