I am about to set up a multisite site and I am thinking of using one database only. My concern is that if the tables of the parent site are replicated for each child site this could make the number of tables in a database extreme massive and cause the database to function very slowly once there are alot of sites in an installation.

I am hoping that only the area of the database that relates to each site is queried when using a user uses a site, rather than the entire index of the database being search whenever any site is interacted with - which would be a drain on computer resources also.

Please advise.

The decision to put website in the same DB or in different DB is generally driven by the MySQL server configuration and the "maximum open file" parameter.

This MySQL parameter define the maximum of files that MySQL can open.

To compute the number of files that is consumes by a DB, you need to multiply the number of tables by 3 because MySQL consume 3 files per table.

Let suppose that 1 website consume 250 tables.
Then 1 site consumer 250*3 = 750 files on the disk.

If your MySQL "max open file" is 7500 then you can put maximum 10 websites into the same DB.
Why we advice to never exceed the number ?
The reason is for the backup.
When you perform a backup, the MySQLDump (or other) needs to look all the files during the backup (and therefore needs to access them).

A large number of open file also require a lot of memory for MySQL.

So, check the value of the "max open file" in MySQL.
If you are the administrator of the server, we recommend to never exceed 100,000 open files (100K).

Depending on the number of tables in a website, in general, you can put 25 websites in a same DB and never exceed 100 websites into a same DB.

I assume them that in create a multisite system with hundreds or thousands of sites its best to use multiple databases. So my question, should this be one database per site. Of can it be up to 100 sites per database. Because using one database for ever site also would get out of hand very quickly.

There is no fix result.
You have to compute the value based on the MySQL maximum of open file and number of tables in a website.

As you didn't provided those number, I can not do the computation for you.

Just apply the forumula