[wp-hackers] Comment administration inefficiencies - Proposing core change

[Kenton Jacobsen]

Recent listener, first time caller.

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

I believe that will do what I'm looking for. Adding efficiency for
everyone--particularly those who have lots of comments. 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.

I am planning on modifying our core for this support, but I would like to
contribute it to the community as well. Obviously I would be happy to
submit a patch of my implementation.

-Kenton Jacobsen