Possible performance issues implementing multi-tenant database?

Question

I would like to design and develop school application using PHP and MYSQL as backend. The frontend - a combination of HTML, JS and CSS - has a registration form for every school.

Every school has its own students, classes, teachers and the like. I would like to use a single database with one students table having a client column that refers to the owner of every record.

e'g: sutd_id, client_id, first_name, last_name......

e'g: class_id, client_id, description, blahblah....

Question 1

Will a WHERE clause with too many parameters affect performance? I mean thinks like WHERE id='blah' AND class_ID='blah' AND client_id='blah' AND enroll_date between 'time' AND 'AGO'.

If the case that affects performance, I've thought of creating views for every new client, example: When new successful registration takes place, I will create some extra views to the database by using new client's unique name.
e'g: new client which is abc is registered than automatically i will create these views: (abc_students, abc_classes....)

Question 2

Would this approach (the one with the views) affect the performance or accuracy of results?

Question 3

Is there another way to increase the performance, other that creating views?

Please rephrase the title of the question. It's broken english and it's confusing. It looks like you are asking what multi-tenant means, but then reading the question one knows that's not what you are asking. Also not clear what the question is. — Tulains Córdova, Apr 26 '16 at 11:40
@TulainsCórdova : may be my title would be how to start SaaS with php and mysql. — superuserdo, Apr 26 '16 at 11:42
That title would cause the question to be closed as off-topic because where-to-start questions are frowned upon. — Tulains Córdova, Apr 26 '16 at 11:43
@TulainsCórdova the fault is for my poor English, sorry for the inconvenient — superuserdo, Apr 26 '16 at 11:45
This question is probably more suited for dba.stackexchange.com. — Lie Ryan, Apr 26 '16 at 15:55

score 1 · Answer 1 · answered Apr 26 '16 at 11:44

1

Views will not help with performance; they are effectively just queries with a Name. Indeed, they may actually make things worse and, IIRC, MySQL does suffer from this "View overhead".

Indexes are what you're looking for.

Sensible indexing can make millions of rows just as responsive as a handful of them.
Don't get carried away, though. Whilst well-chosen indexes will speed up selects, they need to be amended by every insert and delete (and, possibly, update) so the more of them that you have, the slower your update traffic might become.

Indexing, like most things database-y, is a "balancing act". You need to try things out and see what's "best" for your needs.

answered Apr 26 '16 at 11:44

Phill W.

11,891
4
21
36

In some RDBMS, like Oracle views do improve performance since they are like telling the RDBMS: "hey, I will be running this query a lot". The optimizer doesn't have to calculate the fetching strategy everytime and it stays in the SQL cache longer. – Tulains Córdova Apr 26 '16 at 13:46
@TulainsCórdova that may be true for some RDBMS, but not Oracle -- it's ability to cache the SQL plan is not affected by whether the query uses views. – David Aldridge Nov 24 '16 at 09:22

score 0 · Answer 2 · answered Apr 26 '16 at 13:16

If you want to partition the data by school/client, you'll probably want to look at schemas instead of views. A schema will put the tables (and indexes, triggers, etc) in a separate namespace. Depending on the database you use, you may be able to put individual schemas in separate files, which should give you the ability to put individual schools/clients on separate drives if one or more of them grows large enough to require that. Note, however, that while you can use the same data model in your application for all schemas, if you change a table definition you will have to change the table in each schema individually.

Possible performance issues implementing multi-tenant database?

2 Answers2