Comment search isn't customizable
|Reported by:||brokentone||Owned by:|
*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:
- Adding "search_type" and "search_field" to the WP_Comment_Query::query_vars data structure in wp_includes/comment.php
- Replacing WP_Comment_Query::get_search_sql with something more robust, able to understand these new properties and construct the query on their basis
- Upgrading WP_Comments_List_Table::prepare_items to accept the new queries and add them to the data structure it creates
- 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
- 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?
Change History (28)
- Summary changed from Comment administration doesn't scale - Proposing significant change change to Comment search isn't customizable