Filling name causes thousands of queries

[tuomassalo]

Short description of the issue

When there are n pages with the same title, finding a name for the next one causes n queries. This is slow when n>1000.

Expected behavior

I hope there was a way to give up on sequential numbering after a configurable number of tries.

Actual behavior

I have thousands of pages that are created without an explicit, unique(ish) title. Instead, each page will have something like Board member as its title. When a new page is created, PagesNames.php tries to find a name for the new page, causing thousands of queries.

SELECT COUNT(*) FROM pages WHERE name='board-member-1799' AND parent_id='1234'
SELECT COUNT(*) FROM pages WHERE name='board-member-1800' AND parent_id='1234'
SELECT COUNT(*) FROM pages WHERE name='board-member-1801' AND parent_id='1234'

Yes, I could probably fix this outside of PagesNames.php, but this would require lots of changes in our large codebase. I'd like to use a fix that fixes the problem in all occasions. Currently, I've worked around this with a patch:

--- wire/core/PagesNames.php	2024-11-13 21:16:53
+++ wire/core/PagesNames.php	2024-11-27 14:50:12
@@ -491,8 +491,16 @@
 			}
 		}
 		
+		$counter = 1;
+		$originalName = $name;
 		while($this->pageNameExists($name, $options)) {
-			$name = $this->incrementName($name);
+			if(++$counter > 5) {
+				// If we’ve tried 5 times and still can’t get a unique name, just append a random number.
+				// This prevents pageNameExists() from being called thousands of times.
+				$name = $originalName . $this->delimiter . mt_rand(1e9, 9e9);
+			} else {
+				$name = $this->incrementName($name);
+			}
 		}
 		
 		if(strlen($name) > $this->nameMaxLength) $name = $this->adjustNameLength($name);

Can you think of a better fix?

Steps to reproduce the issue

1. Create 10 pages with the same title

2. When creating the 10th page, observe SQL queries like these:

   ...
   SELECT COUNT() FROM pages WHERE name='foo-7' AND parent_id='1234'
   SELECT COUNT() FROM pages WHERE name='foo-8' AND parent_id='1234'
   SELECT COUNT(*) FROM pages WHERE name='foo-9' AND parent_id='1234'

Environment

• ProcessWire version: newest dev