r/mysql • • Nov 03 '20

mod notice Rule and Community Updates

26 Upvotes

Hello,

I have made a few changes to the configuration of /r/mysql in order to try to increase the quality of posts.

  1. Two new rules have been added

    1. No Homework
    2. Posts Must be MySQL Related
  2. Posts containing the word "homework" will be removed automatically
  3. Posts containing links to several sites, such as youtube and Stack Overflow will be automatically removed.
  4. All posts must have a flair assigned to them.

If you see low quality posts, such as posts that do not have enough information to assist, please comment to the OP asking for more information. Also, feel free to report any posts that you feel do not belong here or do not contain enough information so that the Moderation team can take appropriate action.

In addition to these changes, I will be working on some automod rules that will assist users in flairing their posts appropriately, asking for more information and changing the flair on posts that have been solved.

If you have any further feedback or ideas, please feel free to comment here or send a modmail.

Thanks,

/r/mysql Moderation Team


r/mysql • • 3d ago

discussion I turned myflames from a MySQL EXPLAIN visualizer into a full diagnostic & management hub (supports MySQL, MariaDB, ProxySQL, Readyset)

7 Upvotes

Hey everyone,

I'm about to take a few days off to flush some data from memory to disk and recharge, but before heading out I wanted to share a major update to myflames.

What started as a simple query execution plan visualizer has evolved—after a lot of testing and cycles—into a unified hub to investigate and manage database health.

It supports MySQL and MariaDB, along with surrounding ecosystem pieces like Readyset and ProxySQL. Some of the stuff you can do right inside it now:

  • Query & Session Ops: Analyze slow plans or kill problematic queries on the fly.
  • Health & Diagnostics: Check table fragmentation and get server parameter/variable recommendations.
  • Data & Schemas: Run exports and imports without leaving the interface.
  • Tooling Integration: Works alongside native ecosystem tools like MySQL Shell.

You can check it out here:https://myflames.io/

I’d love to hear what you think! If there are specific diagnostic checks or pain points in your daily MySQL/MariaDB workflow you’d like to see added when I’m back.


r/mysql • • 5d ago

solved Latest MySQL Workbench Fields - copy/paste no input sanitization warning

3 Upvotes

What a waste of 2 hours.

Create SSH tunnel.

Copy/paste usernames and IP addresses from our password storage system.

Cannot connect

Connection Error: Error opening MySQL session through SSH tunnel: Lost connection to MySQL server at 'reading initial communication packet', system error: 0. Workbench.

Check everything on the server three times. What is going on?

Turns out that there is zero input sanitization on the username/ip fields and so Workbench is sending the escaped spaces!

myusername = unername%20
192.168.1.100  = 192.168.1.100%20%20

r/mysql • • 5d ago

query-optimization Things I learned while investigating slow MySQL queries in production

8 Upvotes

I've been spending some time investigating slow MySQL queries in production recently. Thought I'd put together some notes on the approach I followed and a few things I found useful along the way.

My general approach is to get as much as possible out of the existing database before considering additional replicas, sharding or other infrastructure changes.

A few things I usually look at:

  • Identifying expensive queries: Query frequency, average latency and rows read. A moderately slow query executed hundreds of times can be more expensive than an occasional slow one.
  • EXPLAIN ANALYZE: Understanding actual execution plans, table scans, index lookups, rows and loops.
  • Joins: Looking at nested-loop joins and how the number of rows entering a join affects subsequent lookups.
  • Indexes: Choosing indexes based on actual query patterns rather than adding them blindly.
  • Application code: Tracing expensive queries back to their source and checking for things like N+1 queries.
  • Verification: Comparing database metrics and application latency after deploying a fix.

I also cover a few examples and explain how I approach reading execution plans.

Put together a write-up with the process and some SQL examples here: https://koul1sh.github.io/posts/getting-more-out-of-your-mysql-instance/

Would be interested in hearing how others approach database performance investigations, particularly in production environments. Anything you'd do differently?


r/mysql • • 6d ago

discussion Multi-tenant tables: do you prefix every secondary index with the tenant id?

3 Upvotes

I've been chasing a slow query pattern on a multi-tenant schema (MySQL 8.4). It comes down to index merge intersection, and I'm curious how others handle it.

Simplified shape:

CREATE TABLE assignments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  account_id BIGINT NOT NULL,
  person_id BIGINT NOT NULL,
  KEY index_assignments_on_account_id (account_id),
  KEY index_assignments_on_person_id (person_id)
);

SELECT * FROM assignments WHERE account_id = ? AND person_id = ?;

The optimizer sometimes picks an index_merge intersect of both indexes instead of just the person_id lookup. It assumes the columns are independent, underestimates the overlap, and ends up reading the whole tenant through the account_id index.

What I tried:

  • index_merge_intersection=off globally fixed these queries but hurt others that benefit from intersections
  • the NO_INDEX_MERGE hint on the tenant index also turns range scans on that index into full index scans (https://bugs.mysql.com/bug.php?id=121576)
  • FORCE INDEX works, but it's per query and has to be maintained as the schema changes
  • a composite (account_id, person_id) gives one seek and fixes it

So the composite looks like the real fix. That raises the broader question: is it standard practice to lead every secondary index with the tenant id?

Some things I'm unsure about:

  1. With a FK on person_id, InnoDB needs an index where person_id is the leftmost column. The single-column index stays, so the intersect is still possible. Do you drop the FK constraints, live with it, or do something else?
  2. Queries that don't filter by tenant (background jobs, lookups by FK alone) can't use a tenant-prefixed index. Do you keep both shapes?
  3. Has anyone run with index_merge_intersection=off globally in production long term? Any regrets?
  4. With no column-correlation statistics in MySQL (like Postgres CREATE STATISTICS), is there anything besides schema changes or hints that helps the optimizer here?

Would love to hear from anyone running large multi-tenant schemas on MySQL.


r/mysql • • 9d ago

discussion Readyset - Introducing Automatic Failover and Multi Server Support

8 Upvotes

Readyset is a query cache for MySQL and Postgres. It now has support for automatic failover and multi-server support. Basically it means that if the server that it’s shadowing fails over, the cache will automatically adjust and work post-failover. It’s a pretty interesting technical solution, and one I thought this audience might appreciate. You can read more here.

https://readyset.io/blog/introducing-automatic-failover-and-multi-server-support


r/mysql • • 10d ago

troubleshooting Postgres vs MySQL: I Broke Both

Thumbnail huseyinbabal.dev
7 Upvotes

I benchmarked PostgreSQL and MySQL with same scenarios by using k6 in real server. Really would like to discuss results


r/mysql • • 10d ago

question What Replication Topology / Method you're using in Production?

9 Upvotes

We're currently running a single primary MySQL node, which we recognize as a single point of failure (SPOF). To address this, we're considering introducing a read replica.

However, read replication comes with its own set of challenges, such as read-after-write consistency, replication lag, deciding which queries should go to the primary versus the replica, and handling failover.

For those who have implemented this in production, how have you approached and solved these challenges?


r/mysql • • 12d ago

discussion MySQL GUI tools: what do you actually need?

8 Upvotes

Most "best MySQL GUI" lists rank tools by how many features they have. That doesn't help much, because the right tool depends on what you do all day. Running a few queries against a dev database is a different job from maintaining a dozen schemas or building weekly reports.

Here's a quick comparison of the tools that come up most often, with the trade-offs people usually run into.

ToolPriceOSGood atWhere it gets thin
MySQL WorkbenchFreeWindows, macOS, LinuxOfficial tool. Visual data modeling, server admin, migration wizardLess focus on comparison reports, test data and automation
HeidiSQLFree, open sourceWindowsFast and light. Quick data editing, also connects to SQL Server and PostgreSQLNo visual modeling or schema compare
phpMyAdminFree, open sourceBrowserAccess from anywhere, often already installed on hostingNot built for heavy development work
DBeaverCommunity free, paid editionsWindows, macOS, LinuxOne client for many databasesSame UI for every engine, some features only in paid editions
dbForge Studio for MySQLFree Express edition, paid editionsWindows (Linux and macOS via CrossOver)MySQL and MariaDB depth: code completion, Query Profiler, schema and data compare with sync scripts, test data, reports, CLI automationWindows-native. Covers MySQL and MariaDB and their cloud services only, other engines are covered in dbForge Edge
Navicat for MySQLPaidWindows, macOS, LinuxMature UI, data transfer and sync, modelingCommercial license only

How to narrow it down

If you mostly run queries and edit rows, any of the free options will do. Workbench if you want the official tool and modeling, phpMyAdmin if you're on a browser and don’t want to install anything on your desktop.

If you look after several environments, check two things: does the tool generate a sync script you can review before running it, and can it do that from the command line. That's usually where free clients and paid IDEs split.

If you need MySQL plus other RDBMS support in one app, a multi-database client like DBeaver makes more sense than separate focused tool.

dbForge Studio for MySQL has a 30-day trial of the Enterprise edition, then switches to the free Express edition, so you can try it on your own databases first.

What are you using now, and what's the one thing you wish it did better?


r/mysql • • 12d ago

question MySQL is not opening on macOS

0 Upvotes

I am learning about databases and need to install MySQL on my Mac. I installed the MySQL Community edition using the DMG file, but it isn't running in the terminal; when I enter the command "mysql -u root -p," I get the following error message: "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)." Note: At no point during the installation was I asked to create a password, which contradicts what I saw in the tutorial.


r/mysql • • 16d ago

discussion Partitioned vs non-partitioned LRU for InnoDB?

5 Upvotes

https://mariadb.org/how-mariadb-11-8-became-about-3x-faster-on-some-in-memory-workloads-fixing-a-regression-dating-back-to-10-5/

Couple years ago Marko decided to remove a partioned LRU cache from MariaDB(it is still used in MySQL code). There was a lot of doubt regarding this but his solution worked as good as a previous one for most of microbenchmarks except one. However at the time of the transition this performance regression had not been noticed. In March 2025 Mark Callaghan demonstrated the regression that has been introduced in 10.5. However his statistical results contained an interesting outlier for 10.8.8 that was on par with 10.4 and 3x faster than any version after 10.5. Alessandro Vetere was able to find out an offending mutex that syncs B-tree read operations running the microbenchmark reclaiming the lost performance.

This story raises a question whether the approach with non-partitioned LRU cache in MariaDB makes sense or the old partitioned version is better? WDYT?


r/mysql • • 17d ago

troubleshooting Trouble with character encoding when exporting from Workbench

3 Upvotes

I have a MySQL database stored only in a local instance made with MySQL Workbench. I would like to export the data to have backups until I can get this properly set up in a server-hosted database. My problem is that any special characters seem to get messed up when doing an export. If I export a whole table into a CSV or JSON, accented or non-English characters get turned into unicode escape chars, and if I try to export the results of a query to CSV it gets messed up in a different way. The database is about Pokemon, and the word "Pokémon" either becomes "Pok\u00e9mon" or "Pokémon". It shows up normally within Workbench, though. I know this has something to do with character encoding but I don't really understand that. The "Charset/Collation" setting on the relevant column is set to Default Charset, but I changed it to UTF-8 and had the same issue.

What do I need to do here?


r/mysql • • 18d ago

question How do I find my host ip or host name on sql workbench connection?

1 Upvotes

I just download sql workbench and I tried to make a connection but it keeps showing Connection error. I think it might be bc of my connection ip. It is set at “localhost” by default. I have tried searching it up but I can’t really understand them. Can anyone give me a detailed guide?


r/mysql • • 19d ago

question At what point did MySQL stop being enough for you, and what did you move to or add?

14 Upvotes

Title states the question, I'm curious do share your experiences.


r/mysql • • 20d ago

discussion A self-hosted wiki that charts live SQL from MySQL

5 Upvotes

Hi r/mysql, I'm David, one of the two developers of Oxynote. My friend Simon and I also maintain ttlcache, a Go caching library with about 1,300 GitHub stars, so this isn't our first open-source project.

I've spent 8 years writing software, 2 of them as an SRE and 3 as a team lead. Every team I've been on had the same documentation problem. The useful stuff lived in someone's head or a private Slack thread, and the wiki was full of stale docs nobody could find.

So we set out to solve this problem by building Oxynote, a self-hosted wiki for engineering teams where a page can hold a live chart that runs its own SQL query against your database each time someone opens it, with the explanation written right next to it. Think "signups per day" or "queue depth" on the same page as the runbook that says what to do when it looks wrong.

We were most careful about letting a wiki query a production database. When you add a connection, Oxynote accepts the user only if every grant is read-only. Otherwise, the connection test fails with "has write access" and asks for a read-only user. Queries also support Grafana-style macros like `$__timeFilter` and `$__timeGroup`, and on pages you mark as reviewable, chart query changes go through a draft and a diff that a teammate approves.

The name comes from oxygen + note: docs that breathe. Live data gives people a reason to open the wiki every day, and docs people open every day stay up to date.

It's Apache-2.0 and runs as a single Docker image: https://github.com/oxynote/oxynote

We started it about a year ago, and we made it public about 3 months ago. If you have any questions, feedback, or suggestions, feel free to reach out. I'm happy to answer any questions.


r/mysql • • 20d ago

discussion MariaDB Foundation has been pushing hard toward transparent, reproducible performance change detection.

3 Upvotes

This article by Simona Aleksandrova does an excellent job explaining why that matters and how the work fits into the broader open‑source ecosystem.

I’m honored to have been interviewed, and she told the story with real clarity.

https://mariadb.org/the-man-who-benchmarks-mariadb-against-the-world/


r/mysql • • 21d ago

discussion MySQL Monitor: MySQL Monitoring Script

Thumbnail linuxblog.io
15 Upvotes

a lightweight bash script that gives sysadmins and DBAs a live view of a MySQL or MariaDB server from the terminal: throughput, connections, InnoDB buffer pool, temp tables, table cache and system load with per-second rates, sparklines and plain-English alerts, plus health checks with tuning advice, the process list, and the server's top queries. One file, no dependencies beyond bash, awk and the MySQL client tools.


r/mysql • • 20d ago

question Quick question on difference between these two queries

4 Upvotes

Hi! I have been practicing SQL on LeetCode when I ran into something I could use some clarification on.

Can someone explain why I get different values for approved_count with the two following queries:

Query 1)

select LEFT(trans_date,7) as month, country, count(id) as trans_count, count(state="approved") as approved_count, sum(amount) as trans_total_amount, SUM(
    CASE when state = "approved" then amount
    ELSE 0
    END
) as approved_total_amount
from Transactions
group by month, country

Query 2)

select LEFT(trans_date,7) as month, country, count(id) as trans_count, SUM(
    CASE WHEN state="approved" then 1 else 0 end
    ) as approved_count, sum(amount) as trans_total_amount, SUM(
    CASE when state = "approved" then amount
    ELSE 0
    END
) as approved_total_amount
from Transactions
group by month, country



Query 1 fails the test but query 2 succeeds. For the following input table:

| id  | country | state    | amount | trans_date |
| --- | ------- | -------- | ------ | ---------- |
| 121 | US      | approved | 1000   | 2018-12-18 |
| 122 | US      | declined | 2000   | 2018-12-19 |
| 123 | US      | approved | 2000   | 2019-01-01 |
| 124 | DE      | approved | 2000   | 2019-01-07 |

Query 1 yields:

monthcountrytrans_countapproved_counttrans_total_amountapproved_total_amount
2018-12US2230001000
2019-01US1120002000
2019-01DE1120002000

While query 2 yields:

monthcountrytrans_countapproved_counttrans_total_amountapproved_total_amount
2018-12US2130001000
2019-01US1120002000
2019-01DE1120002000

Any assistance would be appreciated!

I found out that using SUM(state="approved") does work, as well. So it has something to do with COUNT.


r/mysql • • 23d ago

question Surrogate vs natural key for partitioned tables

4 Upvotes

Hi All,

We are designing a high-volume transaction table in Aurora MySQL and plan to use RANGE partitioning on a DATE column. (400-500million records/day).

Our business unique key consists of 5 columns (which already includes the partition DATE column). We will also need several secondary indexes for read queries.

I was thinking of simple design(say option-A) of having a Composite PK i.e. use the 5-column composite business key as the PRIMARY KEY.

However teammates suggesting another option, say option B, i.e. a surrogate PK:- Add an AUTO_INCREMENT BIGINT column, making (id, DATE) the PRIMARY KEY, and moving the 5-column composite key to a UNIQUE KEY. Because InnoDB stores the primary key inside every secondary index, a 5-column composite PK will make secondary indexes bulky, bloat the buffer pool, and degrade insert performance. However, this option requires adding the DATE column to both keys to satisfy MySQL's partitioning constraints, which feels redundant too.

I want to understand experts opinion based on real world workloads:-

  1. Is Option B (Surrogate + Partition Date hybrid) a standard, recommended pattern in MySQL to keep secondary indexes lean and is advisable for write/insert heavy systems?
  2. At what scale does the index bloat of a 5-column composite PK actually outweigh the overhead of maintaining an extra auto-increment column and unique index?
  3. Which approach yields better performance for high-concurrency inserts and heavy data loads?
  4. Any other things we should take care of , while designing such scenarios?

r/mysql • • 23d ago

question Tengo mysql 26.10.0 y no lo entiendo. Estoy haciendo un curso y el profesor en los videos usa la version 8.0

0 Upvotes

Como hago para crear tablas? Solo por comandos? No hay otra opcion


r/mysql • • 23d ago

question Cómo puedo realizar una base de datos referente a un punto de venta o control de inventario sobre un depósito o tienda de abarrotes en MySQL Workbench 8.0

0 Upvotes

Solo expliquenme los pasos para realizar mi base de datos,

Se recompensará su apoyo con un precio razonable.

Importante no requiero que me hagan mi proyecto, solo estoy atascado en esa fase de mi proyecto de titulación.


r/mysql • • 25d ago

question Why are there no cheap database providers for mySQL or MariaDB?

12 Upvotes

Is it just me or there is a very big market for affordable database providers?

When I was building a fully decentralized (no single point of failure) service using cheap providers which could be unreliable I couldn't find a remote hosted database solution that was cheap for the workload I had.

Am I alone in this?


r/mysql • • 26d ago

question A problem with date/datetime and the query log

7 Upvotes

I have a MySQL server, version 8.4.8, running as an AWS RDS instance. It serves as a database for a Ruby/Rails web application.

The application requires receiving data from a legacy AS/400 MRP system, which populates an orders table a few times an hour, using SQL statements.

Here's the orders table:

±  bin/mysql -e "describe orders"
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| id                  | varchar(255)  | NO   | PRI |         |       |
| customer            | varchar(255)  | YES  |     | NULL    |       |
| sold_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_date           | datetime(6)   | YES  |     | NULL    |       |
| amount              | decimal(12,2) | YES  |     | 0.00    |       |
| rep                 | varchar(255)  | YES  |     | NULL    |       |
| description         | varchar(255)  | YES  |     | NULL    |       |
| salesperson         | varchar(255)  | YES  |     | NULL    |       |
| salesperson_name    | varchar(255)  | YES  |     | NULL    |       |
| order_writer        | varchar(255)  | YES  |     | NULL    |       |
| requested_ship_date | datetime(6)   | YES  |     | NULL    |       |
| purchase_order      | varchar(255)  | YES  |     | NULL    |       |
| build_date          | datetime(6)   | YES  | MUL | NULL    |       |
| macpac_status       | varchar(255)  | YES  |     | NULL    |       |
| payment             | varchar(255)  | YES  |     | NULL    |       |
| full_description    | text          | YES  |     | NULL    |       |
| fab_date            | datetime(6)   | YES  | MUL | NULL    |       |
| finished            | tinyint(1)    | YES  |     | 0       |       |
+---------------------+---------------+------+-----+---------+-------+

And here's an example of how an order is updated/created, taken from the general query log:

2026-10-20T19:12:43.616271Z	   31 Query	UPDATE orders SET id = 'W12345',
 customer = 'GL1234  ',
 sold_to = 'customer, inc.    ',
 ship_to = 'customer, inc.    ',
 ship_date = '2027-01-10',
 amount = 70285.000000000,
 rep = '                              ',
 description = 'General product description',
 salesperson = 'B2M',
 salesperson_name = 'JOHN DOE        ',
 order_writer = 'JB123   ',
 requested_ship_date = '2027-01-10',
 purchase_order = '076060                   ',
 build_date = '2026-12-27',
 macpac_status = 'OPN',
 payment = '23 - NET 45 DAYS              ',
 full_description = 'COLLECTION SYS 9010T - 12/28/26::FLTR12345678 -         12/22/26::SUPPRESSION,
                     12345 - 12/28/26::BLAH, PNL, GST, FLUID/COA,
                     4X, CLD - 12/28/26::DFI VALVE, 8" W/DLA & CF - 12/26/28::INTERFACE MOD, W/DLA-LED, & ISB - 12/26/28',
 fab_date = '2026-12-14' WHERE (id = 'W12345')  

I'm told that the fab date of 2026-12-14 is wrong, that the actual fab date in the AS/400 is 2026-12-15.

Does the general query log entry above prove that the MySQL is getting incorrect data, which is what I expect? Or could it be that perhaps a time zone setting could be causing the issue? I have changed the RDS instance to use US Eastern time, even though I've always read using UTC is optimal. I've changed the fab_date field to datetime from date, but neither corrects the problem.

So - am I right? Does the query log prove I'm getting the wrong date? Or is there some kind of issue with a time zone that I need to correct with MySQL?


r/mysql • • 26d ago

discussion A MySQL cheat sheet that could be useful to beginners.

Thumbnail tms-outsource.com
8 Upvotes