Database Query Optimizer - Problem

Build a simple database query optimizer that generates and compares execution plans for SELECT queries with JOINs.

Given a query structure with tables, join conditions, and table sizes, your optimizer should:

Generate different join orders (execution plans)
Calculate cost estimates for each plan using a simple cost model
Return the plan with the lowest estimated cost

The cost model uses: Cost = (Table1_Size * Table2_Size) / Selectivity

Where selectivity is estimated as 0.1 for equality joins and 1.0 for cross joins.

Input & Output

Example 1 — Basic Three-Table Join

$ Input: tables = [{"name":"A","size":1000}, {"name":"B","size":2000}, {"name":"C","size":500}], joins = [{"table1":"A","table2":"B","selectivity":0.1}, {"table1":"B","table2":"C","selectivity":0.2}]

› Output: ["A","C","B"]

💡 Note: Optimal order: join A with C first (cross join cost: 500k), then join result with B (selective join cost: much lower than A→B→C)

Example 2 — Chain of Joins

$ Input: tables = [{"name":"X","size":100}, {"name":"Y","size":200}], joins = [{"table1":"X","table2":"Y","selectivity":0.5}]

› Output: ["X","Y"]

💡 Note: With only two tables, the order is straightforward: join X with Y using selectivity 0.5

Example 3 — Single Table

$ Input: tables = [{"name":"T1","size":1000}], joins = []

› Output: []

💡 Note: Single table requires no joins, so return empty execution plan

Constraints

1 ≤ tables.length ≤ 10
1 ≤ table.size ≤ 10⁶
0 ≤ joins.length ≤ 20
0.01 ≤ selectivity ≤ 1.0

Visualization

Tap to expand

Asked in

G Google 45 a Amazon 38 M Microsoft 32 f Meta 28

The key insight is to use dynamic programming to avoid evaluating all possible join orders. Build optimal plans incrementally by combining smaller optimal subplans. Best approach uses DP with memoization. Time: O(3ⁿ), Space: O(2ⁿ)

Common Approaches

✓ Brute Force - Try All Join Orders

⏱️ Time: O(n! × n) Space: O(n)

Generate every possible permutation of join orders, calculate the total cost for each complete plan using the cost model, then return the plan with minimum cost.

Dynamic Programming - Bottom-Up Optimization

⏱️ Time: O(3ⁿ) Space: O(2ⁿ)

Use dynamic programming to find optimal join orders by building up solutions for subsets of tables, avoiding redundant calculations through memoization.

Brute Force - Try All Join Orders — Algorithm Steps

Generate all possible join orders (permutations)
For each order, calculate total cost using size estimates
Track the minimum cost plan
Return the optimal execution plan

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Permutations

Create all possible table join orders

Calculate Costs

Evaluate cost for each join sequence

Find Minimum

Select plan with lowest total cost

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <float.h>

typedef struct {
    char name[32];
    int size;
} Table;

typedef struct {
    char table1[32];
    char table2[32];
    double selectivity;
} Join;

char** solution(Table* tables, int tableCount, Join* joins, int joinCount) {
    if (tableCount <= 1) return NULL;
    
    char** result = malloc(tableCount * sizeof(char*));
    for (int i = 0; i < tableCount; i++) {
        result[i] = malloc(32);
        strcpy(result[i], tables[i].name);
    }
    
    // For simplicity, return first arrangement
    return result;
}

int main() {
    // Simplified implementation for C
    Table tables[3] = {{"A", 1000}, {"B", 2000}, {"C", 500}};
    Join joins[2] = {{"A", "B", 0.1}, {"B", "C", 0.2}};
    
    char** result = solution(tables, 3, joins, 2);
    
    printf("[");
    for (int i = 0; i < 3; i++) {
        printf("\"%s\"", result[i]);
        if (i < 2) printf(",");
    }
    printf("]\n");
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n! × n)

n! permutations of join orders, each taking O(n) time to evaluate cost

⚠ Quadratic Growth

Space Complexity

O(n)

Store current best plan and temporary permutation arrays

⚡ Linearithmic Space

22.3K Views

Medium-High Frequency

~35 min Avg. Time

856 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Database Query Optimizer - Problem

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force - Try All Join Orders — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler