I am no professional DBA, but I am curious if there are any basic things I could do to future-proof a system that I expect several tables to grow massively on. I am aware of indexing tables based on keys - I wondered if silver stripe creates indexes in anyway? If so do these relate to the default sort or something else I can configure on the data objects?
When you define a DataObject derivative, you can define a static called $indexes which defines the indexes. When SilverStripe builds the database, it also creates these indexes. So yes, you can get performance out of large tables.