WordPress.org

Make WordPress Core

Opened 21 months ago

Last modified 6 weeks ago

#21435 assigned enhancement

wp-includes/comment.php line85 causes slow query due to the non-indexed column

Reported by: matsubobo Owned by: pento
Milestone: Future Release Priority: normal
Severity: minor Version: 3.4.1
Component: Database Keywords: has-patch
Focuses: performance Cc:

Description

Following query is causes slow query if the wp_comment table is huge.

$ok_to_comment = $wpdb->get_var("SELECT comment_approved FROM $wpdb->comments WHERE comment_author = '$author' AND comment_author_email = '$email' and comment_approved = '1' LIMIT 1");                                                         

In my case, I have 600 thousand records in the wp_comments table and the query takes over 10 minutes to complete.

http://matsu.teraren.com/blog/wp-content/uploads/2012/08/a40b1291fd99413dc3057fbe0b792a93.png

To fix this issue, I added index on my running wordpress and returns 0.00sec.

I'll attach the patch for create table file.

Attachments (2)

index.patch (511 bytes) - added by matsubobo 21 months ago.
patch for create table.
21435.diff (868 bytes) - added by pento 3 months ago.

Download all attachments as: .zip

Change History (15)

matsubobo21 months ago

patch for create table.

comment:1 nacin3 months ago

  • Component changed from General to Database
  • Milestone changed from Awaiting Review to Future Release
  • Owner set to pento
  • Status changed from new to assigned

pento3 months ago

comment:2 follow-up: pento3 months ago

  • Keywords commit added
  • Milestone changed from Future Release to 3.9

I like this index. I'd also expect it to speed up large import processes, which make liberal use of comment_exists().

attachment:21435.diff​ updates the patch to apply cleanly against trunk, and fixes a couple of typos in the original.

comment:3 SergeyBiryukov7 weeks ago

  • Focuses performance added

comment:4 pento7 weeks ago

  • Keywords commit removed

On reflection, attachment:21435.diff isn't exactly the right solution. An index on (comment_author,comment_author_email) would be better, but it could get pretty big, and would be slow to create on big wp_comment tables.

Maybe a 50 character prefix index would be a good trade off between size and query performance, but doesn't solve the problem of creating the index.

comment:5 pento7 weeks ago

Related: #14711

comment:6 follow-ups: mdawaffe7 weeks ago

Seems like (comment_author_email,comment_author) would be more generally useful.

comment:7 in reply to: ↑ 2 matsubobo7 weeks ago

Replying to pento:

I like this index. I'd also expect it to speed up large import processes, which make liberal use of comment_exists().

attachment:21435.diff​ updates the patch to apply cleanly against trunk, and fixes a couple of typos in the original.

Thank you for your feedback but It's not a typo.
The parameter '2' in wcomment_author is what I expected to avoid deep index for string.

KEY comment_author (wcomment_author(2)),

Please refer the following MySQL index logic.
https://dev.mysql.com/doc/refman/5.6/en/create-index.html

comment:8 in reply to: ↑ 6 matsubobo7 weeks ago

Replying to mdawaffe:

Seems like (comment_author_email,comment_author) would be more generally useful.

I don't think so.
The column, comment_author is stored almost unique and cardinality is high so I just added index for this column to avoid getting RDBMS's index size big and overhead of updating row.

Adding multi column index to character column leads to deep BTree index.

comment:9 matsubobo7 weeks ago

I'm currently managing 700,000 records in wp_comments table and works fine with this index setting.

Please see the my table information on production.
https://www.evernote.com/shard/s363/sh/09b52bd7-54c0-4ec1-b985-b9cf86cea3fb/6c6a5cc2aa051206c4e375a2ac0274ab

I hope this patch will be applied as soon as possible because this index increase much performance for large scale wordpress site with less cpu and data cost.

comment:10 in reply to: ↑ 6 ; follow-ups: pento7 weeks ago

Replying to 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 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.

comment:11 in reply to: ↑ 10 matsubobo7 weeks ago

Replying to pento:

Replying to 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.

Yes. Japanese.
In this case, the column is not binary so index is constructed with 2 words.
Japanese characters are almost 50,000 words so that index tree would have maximum 50,000^2 node when using depth 2 BTree index.

If other users does not have this kind of performance problem, index length should be minimum.

Last edited 6 weeks ago by SergeyBiryukov (previous) (diff)

comment:12 in reply to: ↑ 10 mdawaffe7 weeks ago

Replying to pento:

Replying to 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

Ah - I didn't think about that. By the way, for the use cases I was thinking about (essentially the others you mention), a single column index on comment_author_email would have been just as good.

comment:13 nacin6 weeks ago

  • Milestone changed from 3.9 to Future Release

I'd like to fix this. However, it is too late in the 3.9 cycle for a schema change.

I'm curious about a few things. Namely:

  • Which index is generally going to be the best balance of speed and size, for what we do now and what we may do in the future? There are some good discussions here and possibly even a consensus, but yet a lot of data to back up a decision.
  • Why haven't others reported this? 600,000 records in wp_comments isn't that unusual. Surely WordPress.com would have noticed this by now? Do giant sites just actively or incidentally avoid the comment_whitelist option? ("Comment author must have a previously approved comment")
Note: See TracTickets for help on using tickets.