[wp-trac] [WordPress Trac] #39603: The more posts with similar names you have, the slower you save the next one

[WordPress Trac]

#39603: The more posts with similar names you have, the slower you save the next
one
-------------------------------------------------+-------------------------
 Reporter:  bisyarin                             |       Owner:
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Awaiting
Component:  Posts, Post Types                    |  Review
 Severity:  normal                               |     Version:
 Keywords:  needs-testing needs-unit-tests       |  Resolution:
  needs-patch                                    |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by bisyarin):

 Hello!
 I've attached patches for "wp-includes/post.php" and for
 "tests/post/wpUniquePostSlug.php".

 I decided to try @rmccue's suggestion and perform natural sort on MySQL
 side, selecting needed rows for sorting with RLIKE. I didn't notice
 serious performance issues with LENGTH or RLIKE while running the tests
 listed below.

 Ten Thousand Posts Creation Tests

 Original trunk code:
 Fresh database on each run: 329 seconds for 1000 objects (average of three
 runs)
 With 10 000 records already in DB: ~2 hours for 1000 objects (estimation
 based on several minutes of work)

 First patch (that works incorrectly):
 Fresh database on each run: 78 seconds for 1000 objects (average of three
 runs)
 With 10 000 records already in DB: 156 seconds for 1000 objects (one run)

 Second patch:
 Fresh database on each run: 79 seconds for 1000 objects (average of three
 runs)
 With 10 000 records already in DB: 214 seconds for 1000 objects (one run)

 Although the second patch works about 40% slower than the first one, it's
 still a nice result comparing to the original performance.

 Unit tests for the issues discussed here have been created. Also I added
 an SQL-queries counting test.

 In "phpunit\tests\post\wpUniquePostSlug.php" file,
 "test_embed_slug_should_be_suffixed_for_pages" test a typo has been found:
 paage -> page

 IMPORTANT QUESTION:
 The original code has a feature of filling the gaps between numeric
 prefixes.
 For example, we have 'post', 'post-2', and 'post-4'.
 In original code the next prefix is '3'. With the second patch it is '5'.

 Should the behavior of filling gaps be in the code with improved
 performance? It's just requires PHP-side processing of all similar slug
 names, this is why I'm asking. Of course, this processing can be smart,
 but still sending all the needed slug names to PHP can slow things down.
 What do you think?

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