[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 matsubobo):

 Replying to [comment:10 pento]:
 > 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.

 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.

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