DEV Community: Seena Sabti

A (very) Short Look at How MySQL Joins Work

Seena Sabti — Thu, 04 Dec 2025 13:00:00 +0000

MySQL uses Nested-Loop Join (NLJ) algorithms to evaluate joins. Simply put, it takes one row from the first table, then scans the next for matches. Add a third table and it loops again. In the worst case, you face O(n²) time complexity 😳. There is good news though, built in optimizations keep real-world performance much lower.

So How Does It Actually Work?

Imagine you have 3 tables that you are joining: t1, t

SELECT *
FROM t1
JOIN t2 ON t1.t2_id = t2.id
JOIN t3 ON t2.t3_id = t3.id;

The pseudocode for the NLJ algorithm is roughly the following:

for each row in t1
    for each t2 matching t1.t2_id
        for each t3 matching t2.t3_id
            return row if join condition is met

Each inner loop runs for every row of the outer loop. You can see how this could get out of hand fast if your tables are big, No WHERE clause, no indexes? Good luck.

How Does MySQL Optimize the NLJ Algorithm?

MySQL uses what the docs call "condition pushdown": the relevant parts of the where clause will be evaluated at each loop level. Meaning, the where condition of t2 will be evaluated on the t2 loop alongside the join condition and so on:

SELECT *
FROM t1
JOIN t2 ON t1.t2_id = t2.id
JOIN t3 ON t2.t3_id = t3.id
WHERE t2.status = 'active';

Here, MySQL won’t join everything and then filter by t2.status. It will filter t2.status = 'active' while it’s looping through t2 and will only pass those resulting rows into the t3 check.

Indexes Save the Day (Most of the Time)

This is why adding selective indexes is so important for optimization, especially with joins. Indexes help MySQL skip over the rows that are irrelevant to the results. Without them, It’s scanning everything over and over.

But even with the best indexes, joins on massive tables are still going to be inefficient. So when you're designing your database, think about the future. Are these tables going to grow? Can you avoid complex joins later? Do you need to denormalize some of your tables? A little planning now saves a lot of pain down the road.

4 Rules to Make MySQL Indexes Blazing Fast

Seena Sabti — Wed, 03 Dec 2025 14:05:18 +0000

This is for application developers using MySQL to build real world apps. Not DBAs, just folks who want their queries to run fast without digging through dense documentation.

abc% ✅, %abc 🚫

Indexes work for column comparisons using =, >, >=, <, <=, or BETWEEN. They also work with LIKE, but only if the pattern doesn’t start with a wildcard.LIKE 'abc%' is fine, but LIKE '%abc' is not.

AND clauses need to match the index prefix

If your query doesn’t use the leading columns of a composite index, MySQL won’t use that index. The order of your WHERE relative to the index matters. The index should be matched from the start meaning you have to hit the beginning of the index chain for it to be useful. Otherwise, MySQL skips it.

CREATE INDEX idx_name_email 
ON users(first_name, last_name, email);

SELECT * 
FROM users
WHERE first_name = 'John' 
  AND last_name = 'Doe';   -- ✅ Uses the index

SELECT * 
FROM users
WHERE last_name = 'Doe' 
  AND email = 'john@example.com';   -- ❌ Does NOT use the index

OR breaks index usage

That is unless all the OR conditions are using the same index. Sometimes MySQL will use an index merge if all conditions have their own indexes. This retrieves the rows using those separate indexes and merges their results into one. This is less effective than using a single scan, and highly dependent on how selective the conditions are or if a full table scan is more efficient.

SELECT * FROM users
WHERE (first_name = 'John' OR first_name = 'Jane');   -- ✅ Uses index


SELECT * FROM users
WHERE (first_name = 'John' OR email = 'john@example.com');   -- 🚫 Cannot use a single index efficiently

Index exists? Doesn’t mean MySQL will use it

Sometimes MySQL doesn't use an index at all even if one is available. This can happen if your index is not selective enough (lots of seeks), meaning scanning the entire table might yield faster results.

Indexes are great, but they can bite you if you're not careful. Follow these rules to get the most out of your indexes.

Use Local LLMs to Eliminate Little Annoying Tasks

Seena Sabti — Tue, 02 Dec 2025 18:48:37 +0000

Over the past year, I’ve been slowly moving many of the little repetitive tasks in my engineering workflow over to local LLMs. These are the tiny chores that show up dozens of times a day and quietly wear you down. Automating them away has been a real blessing.

If you’re a software engineer who wants to eliminate the tedious parts and move faster, then I hope sharing some of my scripts inspires you to streamline your own workflow as well.

The core of my setup is Ollama. It runs several code focused local models. You do need a little power under the hood to run some of these higher param models. On my M4 Mac these models have been fantastic:

qwen2.5-coder:7b runs extremely fast and is more than enough for most tasks.
qwen2.5-coder:14b a bit slower, but best overall reasoning and code quality.

Running models locally gives automatic privacy, instant response, and no api costs or dependencies. Perfect for experimentation and lots of usage.

The Scripts

The following code snippet, is a small python script I use to automatically generate commit messages. It pulls the diff from staged files, prompts qwen2.5-coder:14b and produces one liner commit messages. I commit often, which also helps the LLM with a small, focused context window.

#!/usr/bin/env python3

import subprocess
import json
import sys
import requests

OLLAMA_URL = "http://localhost:11434/api/generate"
MODEL = "qwen2.5-coder:14b"


def get_staged_diff():
    result = subprocess.run(
        ["git", "diff", "--cached"],
        capture_output=True,
        text=True
    )
    return result.stdout.strip()


def generate_commit_message(diff_text):
    payload = {
        "model": MODEL,
        "prompt": (
            "Generate a concise git commit message for this diff. "
            "Return ONLY a JSON object containing: {\"message\": \"...\"}.\n\n"
            f"Diff:\n{diff_text}"
        ),
        "format": {
            "type": "object",
            "properties": {
                "message": {"type": "string"}
            },
            "required": ["message"]
        },
        "stream": False,
        "options": {"temperature": 0.0}
    }

    response = requests.post(OLLAMA_URL, json=payload)
    response.raise_for_status()
    data = response.json()

    try:
        obj = json.loads(data["response"])
        return obj["message"]
    except Exception:
        print("Error parsing structured output.\nFull response:")
        print(data)
        sys.exit(1)


def git_commit(message):
    subprocess.run(["git", "commit", "-m", message])


def main():
    diff = get_staged_diff()
    if not diff:
        print("No staged changes.")
        sys.exit(1)

    print("Generating AI commit message...")
    message = generate_commit_message(diff)

    print(f"\nCommit message:\n{message}\n")
    git_commit(message)

    print("Committed successfully!")


if __name__ == "__main__":
    main()

The following snippet is another python script I use to automatically generate PR descriptions. It compares my feature branch against a base branch, extracts the diffs, and sends them to qwen2.5-coder:14b running on Ollama. The model summarizes the technical changes into a short, high-signal PR description, which I then publish through the GitHub CLI. I also make sure to read the description to see the model capture the intent fully, which it usually does.

#!/usr/bin/env python3

import os
import sys
import subprocess
import re
import json
import requests
import textwrap
import tempfile

OLLAMA_URL = "http://localhost:11434/api/generate"
MODEL = "qwen2.5-coder:14b"

MY_GITHUB_USER = ""
PR_LABELS = []  


def run_git(*args) -> str:
    result = subprocess.run(
        ["git", *args],
        capture_output=True,
        text=True,
    )
    if result.returncode != 0:
        print(f"Error running git {' '.join(args)}:\n{result.stderr}")
        sys.exit(1)
    return result.stdout.strip()


def get_current_branch() -> str:
    return run_git("rev-parse", "--abbrev-ref", "HEAD")


def get_changed_files(base: str, head: str):
    out = run_git("diff", "--name-only", f"{base}...{head}")
    return [line for line in out.splitlines() if line.strip()]

def get_file_diff(base: str, head: str, path: str) -> str:
    result = subprocess.run(
        ["git", "diff", f"{base}...{head}", "--", path],
        capture_output=True,
        text=True,
    )
    return result.stdout.strip()

def generate_description(formatted_changes: str, pr_title: str) -> str:
    prompt = textwrap.dedent(f"""
    You are helping write the description for a GitHub Pull Request.

    PR title:
    "{pr_title}"

    Below is a formatted list of files and their diffs between the base branch and this feature branch.

    Your task:
    - Write a concise and short description explaining what this PR does.
    - Focus on behavior changes, intent, and key technical points.
    - Assume this will go under a heading 'What does this PR do?' already.
    - DO NOT include headings, bullet lists, or checkboxes.
    - DO NOT mention that you are an AI or describe the process.
    - Just output a few sentences or short paragraphs of description.
    - Be as short and concise as possible without sacrificing meaning or quality.

    Here are the changes:

    {formatted_changes}
    """)

    payload = {
        "model": MODEL,
        "prompt": prompt,
        "stream": False,
        "options": {"temperature": 0},
    }

    response = requests.post(OLLAMA_URL, json=payload)
    response.raise_for_status()
    data = response.json()
    desc = data.get("response", "").strip()
    if not desc:
        print("Ollama returned an empty description.")
        sys.exit(1)

    return desc


def create_pr_with_gh_cli(title: str, body: str, base: str = "main"):

    with tempfile.NamedTemporaryFile(mode="w", delete=False) as tmp:
        tmp.write(body)
        tmp_path = tmp.name

    cmd = [
        "gh", "pr", "create",
        "--title", title,
        "--body-file", tmp_path,
        "--base", base,
        "--assignee", MY_GITHUB_USER,
    ]

    for label in PR_LABELS:
        cmd.extend(["--label", label])

    print("Running:", " ".join(cmd))
    result = subprocess.run(cmd, text=True)

    if result.returncode != 0:
        print("gh pr create failed.")
        sys.exit(result.returncode)

    print("PR created via GitHub CLI.")


def main():
    if len(sys.argv) < 2:
        print("Usage: ai_github_pr.py \"PR Title\" [--base main]")
        sys.exit(1)

    args = sys.argv[1:]
    base = "main"

    if "--base" in args:
        idx = args.index("--base")
        try:
            base = args[idx + 1]
        except IndexError:
            print("Error: --base requires a branch name argument.")
            sys.exit(1)

        args = args[:idx] + args[idx + 2:]

    raw_title = args[0]
    head = get_current_branch()

    if not raw_title.startswith(f"[{head}]"):
        title = f"[{head}] {raw_title}"
    else:
        title = raw_title

    files = get_changed_files(base, head)
    print(files)
    if not files:
        print(f"No changed files between {base}...{head}.")
        sys.exit(1)

    chunks = []
    for path in files:
        diff = get_file_diff(base, head, path)
        if not diff:
            continue
        chunk = f"=== File: {path} ===\n\n{diff}\n\n"
        chunks.append(chunk)

    formatted_changes = "\n".join(chunks)
    if not formatted_changes.strip():
        print("No meaningful diffs found to describe.")
        sys.exit(1)

    print("Generating PR description from Ollama...")
    description = generate_description(formatted_changes, title)

    template = """#### What does this PR do?

{description}
"""

    body = template.format(
        description=description,
        branch_name=head,
    )

    print("\n--- Generated PR Body Preview ---\n")
    print(body)
    print("\n---------------------------------\n")

    answer = input("Create PR with this body via gh? [y/N]: ").strip().lower()
    if answer not in ("y", "yes"):
        print("Aborted.")
        sys.exit(0)

    create_pr_with_gh_cli(title=title, body=body, base=base)


if __name__ == "__main__":
    main()

I also use a set of python scripts that query my company's custom "Jira-like" system. They pull project details, divide the items into actionable tasks, embeds linked reference material, and compile everything into a "Cursor instruction document". These scripts are tightly tailored to my workflow, so I’m not sharing them here.

I hope the first two scripts are useful to you, since many of us share similar workflows. And even if they’re not a perfect fit, I hope they at least inspire you to automate the boring parts of your day and free up more time for meaningful, enjoyable work.