For a large set of similar data, you can improve the database performance by creating a new domain group with a new set of tables in the database. To support the new tables, you must then update the schema.
With the data in a new set of tables, the database engine is more efficient in discarding unneeded query information. As when using any well-defined query, the SQL looks only in the new tables for the data and does not search through other types of data before determining what is required. To further improve access to these tables, apply indexes specific to that particular set of data. Creating multiple, smaller indexes for the new tables, improves efficiency for selects, inserts, updates, or deletes.
With the data separated, index changes on one set of data tables does negatively impact performance on other tables. The search is faster because it looks for the specific data only in one list in the new tables and does not have to search through other tables in the database.
You should not undertake this approach without careful analysis and consideration.
If used incorrectly, this could corrupt your data. For more information, see Impact of data segregation using domains.
The new domain group must map to a new set of tables in the database, such as for a specific document type. For more information, see New tables in the database.