[wp-trac] [WordPress Trac] #20487: Comment administration doesn't scale - Proposing significant change change

[WordPress Trac]

#20487: Comment administration doesn't scale - Proposing significant change change
-------------------------+-----------------------------
 Reporter:  brokentone   |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Comments     |    Version:
 Severity:  normal       |   Keywords:
-------------------------+-----------------------------
 *cross-posted to the wp-hackers mailing list last night*


 I work on a large news oriented WP site and first off, it actually scales
 remarkably well. We keep users, comments, and actual posts all in
 WordPress. However, due to our size, administering comments is causing us
 issues at the moment.

 Our last outage was caused by one of our comment moderators doing a simple
 comment search in the admin. The query took 16 seconds to execute, the
 next query locked, and, with our traffic, the DB couldn't handle the
 resulting queued traffic.

 Staying away from discussions of "you should use Disqus" or, "you need
 more X for your mysql server," I think there is significant opportunity to
 improve the way WP handles comment searching. Every comment search takes
 the following form:

 SELECT * FROM wp_comments  WHERE ( comment_approved = '0' OR
 comment_approved = '1' ) AND (comment_author LIKE '%TERM%' OR
 comment_author_email LIKE '%TERM%' OR comment_author_url LIKE '%TERM%' OR
 comment_author_IP LIKE '%TERM%' OR comment_content LIKE '%TERM%') ORDER BY
 comment_date_gmt DESC LIMIT #;

 As we can all see, this is a beast of a query. Even when the term is
 clearly an specific term (say an email or IP), or when the intent of the
 admin is known (e.g. clicking the IP link on a specific comment).
 Furthermore, there are no hooks activated in this process for plugins to
 use to say create an advanced comment search plugin. One might expect
 hooks like those activated in post search to be activated here, like
 "parse_request" and "get_search_query."

 Waxing philosophical, as posts, comments, and users are the three basic
 types of data to be stored and displayed in WP, one would expect them to
 have similar interfaces and functionality. They each have basic
 functionality of being able to retrieve a single item, a list (in full or
 in part), search for an element, or edit a single entry. For this reason I
 don't understand why each of their functionality isn't derived from
 something like an abstract class or an interface. The architecture here is
 also difficult in that you are not able to replace or extend a single
 class to change the functionality.

 Back on target. I propose that we add functionality by which we'll be able
 to search by exact match in addition to the existing full wildcard (left,
 right, or full wildcard is probably excessive) as well as specifying the
 field to search. This would allow my earlier use case of searching by IP
 to look for an exact match in the "comment_author_IP" field only, not
 searching the fulltext of every comment.

 This functionality can be created simply by:

 1. Adding "search_type" and "search_field" to the
 WP_Comment_Query::query_vars data structure in wp_includes/comment.php
 2. Replacing WP_Comment_Query::get_search_sql with something more robust,
 able to understand these new properties and construct the query on their
 basis
 3. Upgrading WP_Comments_List_Table::prepare_items to accept the new
 queries and add them to the data structure it creates
 4. Either modifying WP_List_Table::search_box to have some options of
 advanced search, or dropping a hook so that a plugin can easily modify it
 5. Modifying the WP_Comments_List_Table::column_author to supply the
 correct query string to indicate an IP search

 If I can figure out a way to make this more similar to the signature of
 posts and users, or at least add some hooks at the right places, I can do
 that as well.

 This will add efficiency for everyone--particularly those who have lots of
 comments. I modified our core for this functionality today and we will
 fully QA it tomorrow. I made it on 3.2.1 as that is what we're running
 right now, and there are minimal changes in the affected files and
 functions between 3.2.1 and 3.3.1 and even the nightly.

 I can submit a patch as long as it passes our QA--how should I do that,
 off the nightly?

 Thanks,
 -Kenton Jacobsen

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/20487>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software