[wp-trac] Re: [WordPress Trac] #4365: Deleting a category scales poorly

[WordPress Trac]

#4365: Deleting a category scales poorly
--------------------------+-------------------------------------------------
 Reporter:  markjaquith   |       Owner:  Denis-de-Bernardy
     Type:  defect (bug)  |      Status:  accepted         
 Priority:  normal        |   Milestone:  2.9              
Component:  Performance   |     Version:                   
 Severity:  normal        |    Keywords:  needs-patch      
--------------------------+-------------------------------------------------

Comment(by vladimir_kolesnikov):

 Subqueries? Why? In many cases JOINs are faster (MySQL optimizer does not
 handle subqueries well until 5.x).

 If I understand the idea of wp_delete_term() correcly, its foreach loop
 can be replaced with 5 queries (even 1 if $args is absent).

 The first query gets the IDs of all objects to be affected by term
 removal:

 {{{
 SELECT DISTINCT tr.object_id
 FROM wp_term_taxonomy AS tt INNER JOIN wp_term_relationships AS tr
 USING(term_taxonomy_id)
 WHERE tr.term_id = 'TERM ID HERE' AND tr.taxonomy = 'TAXONOMY NAME HERE'

 }}}

 The second query removes the term:

 {{{
 DELETE
 FROM wp_term_taxonomy AS tt LEFT JOIN wp_term_relationships AS tr
 USING(term_taxonomy_id)
 WHERE tr.term_id = 'TERM ID HERE' AND tr.taxonomy = 'TAXONOMY NAME HERE'
 }}}

 The third one retrieves the objects that were affeceted by DELETE and do
 not belong to the removed term's taxonomy anymore:

 {{{
 SELECT object_id FROM wp_term_relationship WHERE object_id IN ("IDs from
 the 1st query")
 }}}

 Then we do array_diff($result_1st_query, $result_3rd_query) and generate
 an extended INSERT statement (4th query):

 {{{
 INSERT INTO wp_term_relationships VALUES
 ('OBJECT_ID_1', 'DEFAULT_TERM_TAXONOMY_ID', 0), /* ... */ ('OBJECT_ID_N',
 'DEFAULT_TERM_TAXONOMY_ID', 0)
 }}}

 The last query updates term count:

 {{{
 UPDATE wp_term_taxonomy
 SET `count` = `count` + 'number of rows affected by INSERT'
 WHERE term_taxonomy_id = 'DEFAULT_TERM_TAXONOMY_ID'
 }}}

 This is just a proof of concept, I haven't tested it much yet but it seems
 working to me.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/4365#comment:9>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software