JOIN is really a handy feature of SQL databases, but how about large databases (>10GB). Consider three (two-column) tables of numerous-to-many relationship, as you want to get products connected to 1 single situation (e.g. tags of 1 article).

Details (correct me if I am wrong): 1. For JOIN, three tables should fit inside the memory. 2. Single Choose by PRIMARY KEY doesn't consume memory. 3. Whenever we have concurrent many concurrent read connections, excess connection is going to be stored in queue (not making not successful request or overload).

Then, is not it easier to perform three simple Choose queries. This will make the machine a bit reduced, however i believe that it is more effective to handle the entire tables of Gigabyte size.

You can claim that adding more may be the ultimate solution however i think still handling such large tables is difficult with excess RAM.

Restricting actions to simple Choose queries with PRIMARY KEY could be a practical approach to utilize large databases effectively.

If you're declaring it's easier to perform the chooses on three separate tables, then join the information yourself outdoors from the database engine, then you're wrong. The database is going to do a more satisfactory job joining your queries than you are able to. The tables don't all need to squeeze into RAM for any join to operate.

Indexes can be very useful with large databases.

These could help http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

http://www.tizag.com/mysqlTutorial/mysql-index.php

Indexes can differ among database implementations and they've disadvantages too otherwise designed / implemented properly (this is exactly why you do not just index every area).