Databases
StackOverflow2013
Postsdbo
POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOleg
    1. USOleg
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. USMaxim Krizhanovsky
      1. VTFavorite
    3. VO
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. This table or related slice is empty.
      1. VTDownMod
    1. COPointless question without numbers and details. It is obvious that removing stuff from a RDBMS can be more expensive since transactional integrity, dealing with foreign keys etc. is more expensive than in MongoDB. Especially MongoDB is fire-and-forget and you will not notice when the operation is not finished.
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. USAndreas Jung
    2. COIn mongodb safe mode is used, so it should wait until deleting.
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. USOleg
    3. COSince you didn't say what engine you're using (innodb, myisam or something else), it's difficult to say why exactly, but it's known that Mongo does less and delays fsync-ing, therefore gaining speed (and losing records every now and then).
      singulars
      1. POwhy deleting multiple documents in mongdb is much faster that deleting multiple rows in mysql
      1. USN.B.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload