mayfairsql

Mayfairsql

executable for the translation of SQL queries and table definitions to go program with go structs which correspond with the tables and with the RETE networks for executing the queries

building and testing

the targets of the makefile are:

• make clean: clean up generated files and intermediate files
• make : make the supporting code
• make gen: generate the test books.go file from the books.sql code
• make test: run all tests

Algorithm

The processing of a SQL query entails:

1. Parsing and translation: translate the query in its internal form
2. Rewrite: rewrite the query in a form that can be translated to a RETE network
3. Optimization: order the rete network parts such that the most effective conditions are handled first
4. Translate: generate code for the RETE network

Rewrite

In order to use the RETE algorithm, a query like:

SELECT *
FROM companies, contacts, deals
WHERE companies.company_id = contacts.company_id AND
deals.company_id = companies.company_id AND
deals.contact_id = contacts.contact_id;

Must be translated to one with explicit joins, which can be easily translated to the RETE network:

SELECT *
FROM companies
INNER JOIN contacts ON companies.company_id = contacts.company_id
INNER JOIN deals ON deals.company_id = companies.company_id AND deals.contact_id = contacts.contact_id;

Now the equivalent RETE Network consists of the following alpha nodes and beta nodes:

• an1 = AN(companies), an2 = AN(contacts), an3 = AN(deals)
• bn4 = BN(an1, an2, binary-conds: companies.company_id = contacts.company_id) `
• bn5 = BN(bn4, an3, binary-conds: deals.company_id = companies.company_id AND deals.contact_id = contacts.contact_id)

The alpha nodes are annoted with conditions on the one table of the alpha node, e.g. companies.name = 'CNN' The beta nodes are annotated with binary conditions. In the example, the bn4 beta node can have binary conditions on the tables of its child alpha nodes: from an1 the table companies and from an2 the tables contacts. Note that the bn5 beta node can have binary conditions on three tables, 2 tables (companies, contacts) from the bn4 and the deals table from an3. The order of the tables in a binary condition is not important, the RETE network evaluation of a conditions can handle any order.

In order to translate the implicit joins to explicit ones, the binary conditions are examined. A topological order of the tables in the conditions can be defined when adding a direction to the graph of the binary conditions. A topological order for the example can be:

• {(companies, contacts),(contacts, deals)},(deals, companies)}

The choice for the adding the direction in the binary conditions may determine the efficiency of the resulting RETE network. A choice of direction that matches the binary conditions best results in less beta nodes without conditions. Preferably conditions are distributed evenly along the beta nodes.

Guidelines

Normally the rewrite is done manually. Guidelines for this rewrite are found in: https://godofprompt.ai/prompt-library/rewrite-complex-joins

You are an SQL optimization specialist who untangles complex queries and transforms them into clear, performant statements.

Task

Analyze the provided SQL query and rewrite it for clarity and performance. Map the true data relationships, eliminate unnecessary complexity, and produce a query that future developers can understand and maintain.

Context

Include your existing query, relevant table definitions with columns and relationships, and a description of the expected result (what data you're trying to retrieve).

Process

1. Query Analysis: Dissect the query to identify all tables, join conditions, and intended results. Flag problematic patterns:

   • Ambiguous outer joins
   • Unnecessary cross joins creating Cartesian products
   • Nested queries hiding business logic
   • Implicit joins in WHERE clauses

2. Schema Understanding: Review table relationships, primary/foreign keys, and cardinality. Distinguish necessary joins from redundant additions.

3. Business Logic Extraction: Translate the SQL into plain English explaining the actual data retrieval goal.

4. Join Optimization:

   • Replace implicit joins with explicit INNER/LEFT JOIN syntax
   • Eliminate redundant table references
   • Restructure join sequence to reflect logical relationships
   • Remove subqueries replaceable with proper joins

5. Performance Tuning: Ensure joins use indexed columns, filter early on the driving table, avoid functions in join conditions, and eliminate accidental Cartesian products.

Output

Provide your response in this structure:

ORIGINAL QUERY ANALYSIS

• Identified antipatterns and issues
• Tables and relationships involved
• Performance bottlenecks

BUSINESS LOGIC TRANSLATION Plain English explanation of what the query achieves

OPTIMIZED QUERY

-- Clear, well-commented SQL with explicit joins

OPTIMIZATION EXPLANATION

• Rationale for each change
• How the new structure better reflects business logic
• Expected performance improvements

BEFORE/AFTER COMPARISON Key differences highlighting the improvements

Optimization Principles

• Clarity first: Use explicit JOIN syntax, never comma-separated tables in FROM
• Join type justification: Default to INNER; use LEFT/RIGHT/FULL OUTER only when null handling requires it
• Eliminate redundancy: Remove duplicate table references, unnecessary subqueries, and redundant conditions
• Avoid antipatterns: No ambiguous joins, no nested subqueries when joins suffice, clear aliases for repeated tables, join conditions in ON not WHERE