[wp-trac] [WordPress Trac] #21435: wp-includes/comment.php line85 causes slow query due to the non-indexed column

[WordPress Trac]

#21435: wp-includes/comment.php line85 causes slow query due to the non-indexed
column
-------------------------+--------------------------
 Reporter:  matsubobo    |       Owner:  pento
     Type:  enhancement  |      Status:  assigned
 Priority:  normal       |   Milestone:  3.9
Component:  Database     |     Version:  3.4.1
 Severity:  minor        |  Resolution:
 Keywords:  has-patch    |     Focuses:  performance
-------------------------+--------------------------

Comment (by pento):

 Replying to [comment:6 mdawaffe]:
 > Seems like `(comment_author_email,comment_author)` would be more
 generally useful.

 Aye, that sounds like a better idea.

 * In `wp_allow_comment()`, `comment_author_email` is optional, so having
 `comment_author` first would be better. On the other hand, I assume the
 vast majority of sites require an email address.
 * In `check_comment_flood_db()`, `comment_author_email` is used, but
 `comment_author` isn't. I'd expect the `comment_date_gmt` index to be more
 useful here, though.
 * In `WP_Comment_Query::query()`, `comment_author_email` is an allowed
 param, but `comment_author` isn't, so having `comment_author_email` first
 would probably be more useful, depending on how
 `WP_Comment_Query::query()` is being used.

 Replying to [comment:9 matsubobo]:
 > Please see the my table information on production.
 >
 https://www.evernote.com/shard/s363/sh/09b52bd7-54c0-4ec1-b985-b9cf86cea3fb/6c6a5cc2aa051206c4e375a2ac0274ab

 That's interesting, I'm surprised it gets that high a cardinality from 2
 bytes. I'm curious, is the content of `comment_author` on your site
 generally in Japanese? I'm not at all familiar with character distribution
 in Japanese names or writing, but assuming a random distribution, there
 are certainly enough characters in Japanese to provide a better
 cardinality than in the English alphabet.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/21435#comment:10>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform