From your original post you mentioned that the:
I believe a Sitemap.xml is crawled and all new updates are added to the current crawler database. Their search engine database is then used to pick out exact or near matches to present a list of found items.
I noticed that in your sitemap.xml there is no reference to the NEW-Canonical-URL? I could be very wrong by assuming that it would be far better in your sitemap.xml to remove the OLD-URL and replace with the NEW-Canonical-URL.
The logic behind this is that any crawler going to your OLD-URL will be 301'd and update their database with your NEW-Canonical-URL. Also all OLD-URL's (from any source) will be redirected by the .htaccess file.
@revium appears to agree
>>> I believe Google prefers to not have URLS in a sitemap that redirect,
>>> they prefer to have URLs that are not redirected.
I do not know what "dynamic PHP software" you are using but it would help posters with future recommendations.
I also assume you have a database of posts with the following format:
If the above popular structure is similar to yours then I can give further information about how I have a PHP search function:
- if URL keyword has a number (within range of table->id) RESULT: table->title, table->blurb
- if URL has string then search table->title, exact match RESULT: table->title, table->blurb
- parse URL keywords and search table->title and table->blurb RESULT: all matched table->title
- RESULT: 404 page showing keywords not found and possible solutions.
Adapting the above function to your own specific requirements will cater for a numeric reference to a NEW-Canonical-URL.
>>> It's bad to have a number in the URL that points the entry in the database?
As far as the crawler is concerned then the numeric is confusing and cannot be referenced.
Is your four digit number decimal, octal, hexadecimal or something else?