title: why deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
body: <p>I tried to data both in mongodb and mysql. A rows has field list_id, there can be very many rows with one list_id. It looks like deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql. I use innodb engine in mysql.
  Mysql and mongdb are on the same server.
  For example, </p>
  
  <pre><code>DELETE FROM contacts WHERE list_id = 100
  </code></pre>
  
  <p>is much slower than</p>
  
  <pre><code>return self::remove(array('LISTID' =&gt; $listId), array('safe' =&gt; true));
  </code></pre>
  
  <p>I use safe mode for driver in php, so it should wait until it deletes all data.</p>
  
  <p>Here is info about mongodb collection:</p>
  
  <pre><code> "count" : 23456989,
          "size" : 4391452160,
          "avgObjSize" : 187.21295218239646,
          "storageSize" : 5727051776,
          "numExtents" : 32,
          "nindexes" : 2,
          "lastExtentSize" : 961069056,
          "paddingFactor" : 1.0099999999950207,
          "flags" : 1,
          "totalIndexSize" : 2983806672,
          "indexSizes" : {
                  "_id_" : 787504144,
                  "LISTID_1_EMAIL_1" : 2196302528
          },
          "ok" : 1
  }
  </code></pre>
  
  <p>For example, if there 100K rows that meet condition, in mongodb it's about 30 times faster, in mysql it took about 99 seconds to delete all 100K rows that meet this condition.</p>
  
  <p>Indexes are used in both mysql and mongodb.</p>
  
  <pre><code>EXPLAIN SELECT *
  FROM `subscribers`
  WHERE list_id =118
  
  id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
  1   SIMPLE  subscribers     ref     FK_list_id  FK_list_id  4   const   1    
  </code></pre>
  
  <p>I don't make this operations online now, I put data in queue and do it background, deleting data by chunks.</p>
  
  <p>But I wonder why the time of deleting differes so much, something like 20-30 times.
  Is deleting in mongodb is much faster because this operation is not atomic in mongodb?</p>
  
  <p>This is what</p>
  
  <pre><code>SET PROFILING = 1;
  DELETE FROM subscribers WHERE list_id = 118;
  SHOW PROFILE FOR QUERY 1;
  </code></pre>
  
  <p>displays for deleting 100K rows:</p>
  
  <pre><code>starting    0.000052
  checking permissions    0.000000
  Opening tables  0.000000
  System lock     0.000000
  init    0.000000
  updating    84.382015
  end     0.000006
  Waiting for query cache lock    0.000002
  end     0.000006
  query end   0.035284
  closing tables  0.000021
  freeing items   0.000040
  logging slow query  0.000001
  logging slow query  0.000002
  cleaning up     0.000002
  </code></pre>