In RDBMS(MySQL..), the more complexity was added to the query, the higher time the query will take?

Question

The complexity includes these factors:

1. Number of joined tables
2. Number of subqueries
3. Number of aggregate functions
4. Number of groups by, the columns in group by, the window functions
5. Number of where conditions, sorted columns

Assume the number of rows in all tables is fixed(already has appropriate index) so the size of the table doesn't affect query time when we add more join tables to the query

Logically, the more logic we added to the query(like number of joins, subqueries...) which increases the complexity, the more RDBMS(like MySQL) will process, therefore it will take more time to process, am I right?

Thank you in advance,

Answer 1

Logically, the more logic we added to the query(like number of joins, subqueries...) which increases the complexity, the more RDBMS(like MySQL) will process, therefore it will take more time to process, am I right?

Yes and no.

Firstly, this applies to any system, regardless if it's an RDBMS, NoSQL, NewSQL, or otherwise. The more operations that any system needs to process, the more work there is being performed. The overhead for these operations are generally very minimal.

But as Akina mentioned in the comments, the operations may reduce the amount of data (e.g. from JOIN, GROUP BY, WHERE and HAVING clauses, etc.) that needs to be subsequently processed and returned. The savings on data reduction can significantly improve total execution time.

An example of this, where an RDBMS may outperform a NoSQL database because of the additional operational complexity, is with two tables that have a one-to-many relationship, and a few other additionally related tables. If the many table is huge, but you write a query that only returns a single entity from the one side joined to its related many records, the JOIN will reduce the data significantly before any further processing is needed and returns much less data. And if you don't necessarily need the additional data from the other related tables, you wouldn't have to JOIN them in. Conversely a NoSQL database would pre-stage the data fully joined from all of the aforementioned tables already, resulting in a single exploded out table that would likely require more Memory to load off Disk, and more work to search, for just the subset of data you care about.

With a properly designed and architected database, with the correct indexes implemented, all of the aforementioned operations should happen in negligible time (milliseconds or less).