r/SQL

I'm dealing with a performance issue in MySQL where a query runs relatively fast when executed against a single table, but becomes significantly slower after adding a JOIN and an ORDER BY clause.

The tables have millions of rows, and there are indexes on the columns used in the JOIN and WHERE conditions. However, the query optimizer still seems to choose a suboptimal execution plan.

I'm wondering what the best approach would be to troubleshoot this kind of issue.

Would you start by analyzing EXPLAIN ANALYZE, checking index cardinality, investigating temporary tables and filesorts, or looking at something else?

Also, under what circumstances would MySQL choose not to use an existing index even when it seems beneficial?

I'd appreciate any insights into how experienced MySQL developers approach this problem.

For anyone using SQL clients for recurring reports, which DBeaver alternative would you recommend? The workflow I'm interested in is taking an existing query, adjusting dates or filters, checking why the numbers don't match another report, and exporting the results. Things like finding last week's query and keeping track of which database a tab is connected to would matter quite a bit. DbVisualizer, SQL Workbench/J and DbGate are the options I'm looking into. Has anyone used these for that kind of work? Curious about the small annoyances that only become obvious after a few weeks. What would make you choose one over DBeaver, and is there anything you'd lose by switching?

Hi everyone,

I started building `det` (https://github.com/det-Labs/det), an open-source ETL and data engineering framework designed around high-performance native execution, with an embedded data catalog module built directly into the engine core.

### The Problem

In modern data engineering workflows, ETL/transformation engines and data catalogs are treated as completely detached layers. Catalogs (DataHub, OpenMetadata) typically require separate, heavy distributed infrastructure (Elasticsearch, Kafka, JVM services), while transformation engines (dbt, Spark) execute transformations blind to metadata until an external crawler reconciles the state.

`det` combines both: an execution engine where schema detection, metadata cataloging, and data transformation share the same zero-copy Arrow/Polars foundation.

### Current Implementation (Catalog Core Working)

The foundation is ~25% complete, with the data catalog module functional today:

- Ingestion and inspection of storage layouts to extract schema/metadata directly.

- Local metadata indexing with zero background daemon overhead.

- Rust-native performance designed to feed directly into analytical transformation pipelines.

### Roadmap: Transformation & Execution Layer

The next phase expands `det` into a full execution engine:

1. In-memory data transformations leveraging Polars and Apache Arrow zero-copy memory layouts.

2. Direct pipeline execution driven by the cataloged metadata contracts.

3. Plug-and-play connector interfaces for source/sink abstractions.

### Seeking Review & Contributors

Rather than designing the execution engine in isolation, I am looking for feedback from data engineers on:

- **Engine contracts:** Designing the execution DAG to interact natively with the embedded catalog metadata.

- **Modularity:** Interface design for adding custom connectors and storage backends.

Issues are tagged with `good first issue` and `help wanted` for contributors interested in systems-level data engineering tooling.

Repository: https://github.com/det-Labs/det

I just got this thing seeming to work. Is it horrible?

I'm generating an inventory worksheet in 2 coordinated versions: an abbreviated output for printing that goes through my formatting pipeline, and a CSV output with more detail columns intended to open in Excel to type-in the numbers written on the printout. I've just now gotten the CSV output reworked to include embedded formulas for summing counts, multiplying by price, and summing a grand total at the bottom.

The query got pretty gruesome to get the right row numbers to insert into the formulas. First snippet is a function for extracting a usable number from the 'pack' column which may be in several different formats.

CREATE OR REPLACE FUNCTION pack_units(pack text)
RETURNS integer
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE
      WHEN pack ~ '^[0-9]+/[0-9]+$'
        THEN split_part( pack, '/', 1 )::integer
           * split_part( pack, '/', 2 )::integer
      WHEN pack ~* '^[0-9]+ case'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~* '^[0-9]+ loose'
        THEN substring( pack from '^[0-9]+' )::integer
      WHEN pack ~ '[0-9]+/case'
        THEN split_part( substring( pack from '[0-9]+/case' ), '/', 1 )::integer
      WHEN pack ~ '[0-9]+/Case'
        THEN split_part( substring( pack from '[0-9]+/Case' ), '/', 1 )::integer
      WHEN pack ~ '^[0-9]+$'
        THEN pack::integer
      ELSE NULL
    END
$$;

Then here's the horrible query:

DROP VIEW alcohol_worksheet CASCADE;
CREATE VIEW alcohol_worksheet AS
WITH alcohol_products AS (
  SELECT vendor, cat,
       CASE WHEN brand = '' THEN name ELSE brand || ' ' || name END AS brand_name,
       pack || '/' || size AS pack_size, pack_units(pack) AS pack_units,
       case_dollar, CASE WHEN split_dollar = '' THEN to_char( case_dollar::numeric / pack_units(pack), '999D99' ) ELSE split_dollar END AS split_dollar
  FROM total_products
  WHERE vendor in ('Breakthru', 'Craft Republic', 'Glazers', 'Southern Glazers', 'Laszlo', 'Lohr', 'Summit')
        AND is_active
  ORDER BY cat, brand_name
),
numbered_products AS (
  SELECT 1 + ROW_NUMBER() OVER () AS rn,
         vendor, cat, brand_name, pack_size, pack_units,
	 case_dollar, split_dollar
  FROM alcohol_products
)
SELECT rn, vendor, cat, brand_name, pack_size, pack_units,
       case_dollar, split_dollar,
       --I              J               K             L               M             N             O           P       Q
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       '+I'||rn || '+J'||rn || '+K'||rn || '+L'||rn || '+M'||rn || '+N'||rn || '+O'||rn AS row_count,
       '=P'||rn || '*H'||rn AS row_total
  FROM numbered_products
UNION ALL
SELECT NULL AS rn, '' AS vendor, '' AS cat, '' AS brand_name, '' AS pack_size, NULL AS pack_units, '' AS case_dollar, '' AS split_dollar,
       '' AS Storage, '' AS Rooftop, '' AS Overflow, '' AS Cowboy, '' AS Beatniks, '' AS Lizard, '' AS Cabin,
       'Grand Total:' AS row_count, '=SUM(Q2:Q' || MAX(RN) || ')' AS row_total
  FROM numbered_products
;


COPY (SELECT * FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.csv' CSV HEADER;
\COPY (SELECT vendor, cat, Left( brand_name, 50), pack_size, case_dollar, split_dollar, Storage, Rooftop, Overflow, Cowboy, Beatniks, Lizard, Cabin FROM alcohol_worksheet) TO '/home/luser/pgsql/warehouse2/output/Inventory_alcohol_worksheet.tab' WITH (FORMAT TEXT, DELIMITER '|', HEADER, NULL '');

I hate counting up column letters by hand, but that seems to be the nature of the beast. Sigh. Thanks for listening.

Oh, here's some of the output:

$ psql -f scripts/write-inventory-alcohol-worksheet.sql warehouse2

DROP VIEW
CREATE VIEW
COPY 212
COPY 212
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ head output/Inventory_alcohol_worksheet.csv
rn,vendor,cat,brand_name,pack_size,pack_units,case_dollar,split_dollar,storage,rooftop,overflow,cowboy,beatniks,lizard,cabin,row_count,row_total
2,Lohr,Beer,2nd Shift Oktoberfest 6/4 pack 16oz Can,24/16oz,24,42.70,   1.78,"","","","","","","",+I2+J2+K2+L2+M2+N2+O2,=P2*H2
3,Breakthru,Beer,4 Hands Brewing Bright Vibes India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I3+J3+K3+L3+M3+N3+O3,=P3*H3
4,Breakthru,Beer,4 Hands Brewing Incarnation Juiced IPA,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I4+J4+K4+L4+M4+N4+O4,=P4*H4
5,Breakthru,Beer,4 Hands Brewing Incarnation Non-Alcoholic IPA,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I5+J5+K5+L5+M5+N5+O5,=P5*H5
6,Breakthru,Beer,4 Hands Brewing Octohaze Hazy India Pale Ale,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I6+J6+K6+L6+M6+N6+O6,=P6*H6
7,Breakthru,Beer,4 Hands Brewing Octohaze Tropical,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I7+J7+K7+L7+M7+N7+O7,=P7*H7
8,Breakthru,Beer,4 Hands Brewing Oktoberfest Lager,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I8+J8+K8+L8+M8+N8+O8,=P8*H8
9,Breakthru,Beer,4 Hands Brewing Peachy Pipe Dream,24/16 oz can,24,47.97,   2.00,"","","","","","","",+I9+J9+K9+L9+M9+N9+O9,=P9*H9
10,Breakthru,Beer,4 Hands Brewing Pumpkin Spice Ale,24/16 oz can,24,52.77,   2.20,"","","","","","","",+I10+J10+K10+L10+M10+N10+O10,=P10*H10
\]0;~/pgsql/warehouse2
luser@LAPTOP-DE7EDA2U ~/pgsql/warehouse2
$ tail !$
tail output/Inventory_alcohol_worksheet.csv
204,Southern Glazers,Wine,WOODBRIDGE CHARDONNAY CALIF(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I204+J204+K204+L204+M204+N204+O204,=P204*H204
205,Southern Glazers,Wine,WOODBRIDGE MOSCATO CALIF(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I205+J205+K205+L205+M205+N205+O205,=P205*H205
206,Southern Glazers,Wine,WOODBRIDGE PINOT GRIGIO(SC),12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I206+J206+K206+L206+M206+N206+O206,=P206*H206
207,Southern Glazers,Wine,WOODBRIDGE ROSE,12 Case/750ML,12,58.68,   4.89,"","","","","","","",+I207+J207+K207+L207+M207+N207+O207,=P207*H207
208,Southern Glazers,Wine,WOODBRIDGE SAUVIGNON BLANC(SC),12 Case/750ML,12,50.40,   4.20,"","","","","","","",+I208+J208+K208+L208+M208+N208+O208,=P208*H208
209,Laszlo,Wine Slushees,Blue Hawaiian,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I209+J209+K209+L209+M209+N209+O209,=P209*H209
210,Laszlo,Wine Slushees,Orange Mimosa,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I210+J210+K210+L210+M210+N210+O210,=P210*H210
211,Laszlo,Wine Slushees,Raspberry Chardonnay,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I211+J211+K211+L211+M211+N211+O211,=P211*H211
212,Laszlo,Wine Slushees,Strawberry Blanc,2/2.5 GAL,2,100.00,  50.00,"","","","","","","",+I212+J212+K212+L212+M212+N212+O212,=P212*H212
,"","","","",,"","","","","","","","","",Grand Total:,=SUM(Q2:Q212)

Forgive my naivete in advanced.

I am being tasked with setting up 2 major SQL servers with backups and I am terrified. I have never done this and have a loose understanding of things but that's generous.

The reason it's major is because our whole organization that services the state rely on these servers.

The traditional headaches on a Sysadmin side are lack of documentation/none, everyone has left/terminated, no patching, and EoL.

I'm also using Nutanix after coming from Veeam and HyperV.

What I think the approach should be is:

1. Create a new clean test server that is patched and up to date but the same version has what the current server is. Currently this one has a lot of random things on here including some gaming installs...

2. Create a full backup and figure out where that goes not on the current box so I assume I need some sort of SMB or repo? I believe I should create this in SMSS but am not clear if I make the backup if it will start making logs and events that may error if I don't set things up to point correctly if it wants to setup a schedule.

3. Come up with a backup schedule for both fill and incremental.

4. All the backups go to repo.

5. Try the full backup on the new test server and start staging that as a fail over. Or maybe keep it is as test and make a clean fail over?

6. Hopefully figure out what the future state setup is and start figuring out how to port that over into a cloud setup?

I need to have this done by EoM. I am wondering if this can be done in the future in AzureBlob to make things "easier" since this all feeds into PowerBi.

Mostly, I am not sure if there are pitfalls that would bring production down and want to tread carefully cause I'm not sure how to fix things if things go down.

There is no DBA or anyone with SQL server experience on the team...

To monitor the performance and availability for a thick client based system we recently delivered, the customer wants us to provide Here is the requirement:

1. Need to measure and report response time to Azure SQL queries for about 6 scenarios. Session counts, availability, utilization metrics for Azure SQL to be provided.

2. What native monitoring and metrics does Azure Files have to measure response times for files uploaded from devices connected to it.

What metrics needs to be extracted to measure all response times and how to be extracted.

This has to be measured for a period of 72 days with weekly and monthly reporting. Also, we do not have access to SSMS for Azure SQL and also the Azure Portal.

Thus , we need provide instructions to the customer IT Support team and they will provide the metrics and logs.

We have tried to get some help with AI tools but got conflicting response so need an Azure expert preferably Azure SQL DBA to help.

Much appreciated and thanks in advance.

PS: Application is thick client (yes I know) so no metrics can be pulled from endpoint devices. Don't ask why.

Not talking about some crazy DB failure or anything.

Could be something silly like a missing WHERE, wrong JOIN, duplicate rows, bad UPDATE....

Like a small mistake that looked harmless but ended up causing a big headache ....🤕

Curious what ppl have seen in real projects.

For those who have worked with PostgreSQL, MySQL, and SQL Server, what’s a feature or behavior that surprised you because it worked differently between them?

It could be anything like date functions, string handling, LIMIT/TOP, NULL behavior, JSON, window functions, or even error handling.

What difference caused you the most trouble, and how did you learn to handle these database-specific differences?

Fucking comma's in the data that are not the delimiter are annoying. Hate using stupid work arounds.

Yes, I am aware of double quotes and other work arounds like using different delimiters. Just thinking it would be easier to have one standard going forward.

I set up a Postgres MCP server on a read only role last week so the analysts could ask questions without pinging me every hour.

The first real question was how many orders did we lose last month. Claude looked at orders, saw status_cd with values 1 to 6, decided 4 was cancelled and gave a clean number with a nice little breakdown. 4 is refunded. Cancelled is 6. Nobody wrote that down anywhere. It lives in an enum in the app code and in my head.

The number was off by about a third and it read exactly like a right answer. No I'm assuming, no hedge. The analyst was about to put it in a deck.

So now I'm stuck on how to give an agent the meaning of fields without me turning into a full time documentation service:

1. Comments on the columns in Postgres and hope the server passes them through
2. A markdown data dictionary in the system prompt
3. A view layer with human names on everything
4. Make the agent ask before it interprets any code column

What are people actually doing here? and has anyone got it to say I don't know what 4 means instead of picking one?

Product catalogs are often cited as examples of where document databases fit well. A laptop and a T-shirt may share only a few fields, so putting every possible attribute into a single fixed table can result in a wide schema with many empty columns.

A common setup is to keep orders and payments in a relational database and store the catalog in a document database such as MongoDB.

Databricks makes the case for the other route in a recent piece that I was reading on relational vs non-relational databases: keeping the catalog in PostgreSQL and using a JSONB column for attributes that vary across products. That keeps the flexible fields next to the common fields and lets the catalog remain part of the same relational system as orders and payments.

The choice seems to depend on how the data is queried.

If most requests retrieve one product by ID with all its attributes, a document database may fit that access pattern well. If the application needs to filter, group, join, or aggregate across product attributes, keeping the data in a relational system may make those queries easier to manage.

For anyone who has used JSONB for a product catalog, when did it start to become difficult?

Was it indexing fields inside the JSON, inconsistent attribute names, validation, schema changes, or queries that became difficult to maintain?

And for those who moved the catalog to a document database, what made the separate system worthwhile?

I might just be lazy but I feel like it saves me effort.

Hi,
I have an interview with some engineers at a bank, I asked if it was coding, technical or behavioral but wasn't told. First round, going in blind. I am preparing as if I will have to whiteboard. I need guidance on how to prepare with a limited amount of time, as this is my first Data Engineering opportunity ever (I have 1 YOE as a SWE).

Context:
1 YOE Full-Stack Dev, SpringBoot / React / SQL Server. Laid off due to death of my manager. Good with Claude or any AI automation stuff.

Job Description:

**What they're trying to do:** Modernize by moving older data platforms onto cloud based data lakes
Run data migrations
I would just do basic SWE tasks

**Required**: 2 YOE, Python Cert, PowerBI, AZ900 (I have AWS so it's ok)

**Desired**: Tracing data flows and understanding upstream / downstream dependencies.
Analyzing data lineage and system linkages
PySpark or Spark
Python dev / automation
SQL Server / Advanced SQL (Memory, performance, partitioning, query)
Data governance

Bonus (Possible, not listed but I deduced from a similar listing):
RAG architecture, vector stores, embedding pipeline
Use LLMs for Data quality / extraction / automation

**My approach**:

I am good with writing SQL Server Queries up to window functions, aggregates, joins (like at a junior level. I didn't do more at my last job). I am at mid-level with theory though. Anything more I should be prepared to write?

I can write a Python script, but I've not developed in python professionally, have in college though. What basics should I be prepared to write?
I can quickly prepare on any theory questions (PySpark, etc.) Any suggestions? Anything is appreciated, thanks!

Will they stick to Oracle/SQL Server and run it on Linux, or are they starting to look into migrating into Postgress/MariaDB, or something homegrown.

OS migration is straightforward, but not sure about these two behemoths. Or they'll still buy them and just run them locally or on European cloud infrastructure?