As part of a separate conversation Matt suggested something I had not given much thought to before. Say you have a database column that is used to store domain names. Something as simple as `domain` varchar(255) NOT NULL.
Of course you’ll want to search all of that data. Perhaps you want to lookup all of the sub-domains for example.com. That query might look like
[sourcecode lang=”sql”]
SELECT * FROM table_name WHERE domain LIKE ‘%.example.com’
[/sourcecode]
Having a wildcard ( % ) at the front of a LIKE comparison generally results in poor performance. Depending on the database you may find that either it can’t make use of an index on that column, or it must do a full index scan in order to find the right entry. To make better use of an index the wildcard should be at the end of the string, which we could do if we reverse the domain name ordering:
[sourcecode lang=”sql”]
SELECT * FROM table_name WHERE domain LIKE ‘com.example.%’
[/sourcecode]
So that’s what we do. This of course brings up the question of how to correctly reverse the domain name parts. In PHP this can be done with a combination of explode(), array_reverse(), and implode():
[sourcecode lang=”php”]
$domain = ‘joseph.example.com’;
$reverse_domain = implode( ‘.’, array_reverse( explode( ‘.’, $domain ) ) );
[/sourcecode]
That turns joseph.example.com into com.example.joseph. The reversed domain name gets stored in the database and that allows us to use the more efficient database query.