Thread:Talk:Requests for comment/New sites system/Database schema proposal/reply (13)

I mentioned before but I'll try to clarify.

The dot acts as a guarantee that every chunk of the domain will terminate with a dot even when it is the last chunk. It allows us to do partial matches on a domain using a single LIKE instead of both a LIKE and an '' or a LIKE that will make bad matches.

For example if we want to do a match on anything ending in mediawiki.org (which is almost always what you really want) we would do a query like this. Because mediawiki.org is 'org.mediawiki.' this LIKE query will match both mediawiki.org and www.mediawiki.org.

However if we use just 'org.mediawiki' instead we can't do this with one simple test. If we do  then we won't match mediawiki.org without the www. If we do  we will match mediawiki-something.org in addition to mediawiki.org, which is a different domain name. The only option is a long query.

So reversing and postfixing together keep the important parts in the index and make it possible to match the base domain name in a single query (the query we will likely be using the most).

Additionally if databases are using sorting similar to `sort` then it is also important to keep domain names together. org.mediawiki org.mediawiki-something.www org.mediawiki.www org.mediawikisomething.www vs. org.mediawiki-something.www. org.mediawiki. org.mediawiki.www. org.mediawikisomething.www.

Basically without the trailing. an ORDER BY would sort mediawiki.org separately from www.mediawiki.org if a mediawiki-something.org or www.mediawiki-something.org was also in the database.