Best way to index domains and subdomains (test.example.com)

[Dani]

Hi everyone,

I'm trying to build a huge database (800M+ entries) with an index for domain names.

Finding objects with exact domains or domains that start with something is fast as a lightning thanks to the index, but if I try to find "test.example.com" only knowing the "example" part, the regex /example/ takes ages to return the result.

¿Is there any way to index this better? ¿Should i create a tree-like structure for the domains (something like: com -> example -> test), and index every item in it?


Extra info: Even though the database is huge, inserting/updating speed is not really relevant, as it will just be needed once a week or so. Main concern is reading speed and versatility


Thanks a lot.

[Wan Bachtiar]

Should i create a tree-like structure for the domains (something like: com -> example -> test), and index every item in it?

Hi Dani,

The best way is relative to the way your application interacts with the data.

Having said that, storing each domain levels into a separate fields may work well for the use case. Especially if the application often queries based on the top level and the second level domain only. Is there a reason that prevents you from trying this document schema ?

Regards,
Wan.

​