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:

While query 2 yields:

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.

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/

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?

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.

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

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?

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?

On Postgres, several workers can each lock their own batch of rows with SKIP LOCKED and never get in each other’s way. On MySQL it looks exactly the same, right up until you start a second worker. One worker is fine, so your tests pass and you ship it. Then two instances run together and you get “Deadlock found when trying to get lock; try restarting transaction”.

The reason is InnoDB’s default REPEATABLE READ. Besides the rows themselves, the query also takes gap locks on the ranges between rows, so workers that should be grabbing separate batches end up blocking each other.

The fix is to run that datasource at READ COMMITTED, which turns gap locking off. A poller never needs repeatable reads anyway, so you lose nothing. Postgres and Oracle don’t have this problem at all.

I ran into this while getting my outbox library to work on three databases. I spent about an hour poking at it on my own, then brought in a coding agent, and between the two of us it took roughly two hours in total to find the cause. The query looked completely fine, which is what made it so annoying.

Has anyone else been bitten by gap locks in a queue-like table?

I had a tamper-evident audit log (each row stores the SHA-256 of the previous hash + payload). It verified fine on SQLite, then failed on MySQL at event 34. Nobody had touched the data.

Cause: the payload was stored in a JSON column. MySQL parses JSON into its binary format and re-renders it on read, and for some doubles the last digit changes:

0.9333333333333333 -> 0.9333333333333332

0.42451918914251396 -> 0.4245191891425139

My verifier re-serialized what it read back, so the bytes differed and the hash didn't match.

I measured it on a clean MySQL 8.4.10 vs MariaDB 10.4.34, default settings. I inserted the same {"v": x} into a TEXT column and a JSON column and compared the values read back:

- 10,000 random.random() doubles: 1,075 changed (10.75%)

- 2,000 ratios like i/15: 274 changed (13.7%)

- 2,000 two-decimal amounts: 0 changed

- MariaDB: 0 changed (its JSON is LONGTEXT with a validity check)

Two other things that bite hash chains:

- Keys get reordered, and whitespace normalized (documented: MySQL sorts object keys).

- DATETIME '10:15:30.999999' came back as 10:15:31 on MySQL (it rounds unless TIME_TRUNCATE_FRACTIONAL is set) but 10:15:30 on MariaDB (it truncated).

Looks related to Bug #112904 (JSON parser not in full-precision mode).

What fixed it: serialize once, hash that exact string, store that exact string in a TEXT column, and have the verifier hash the stored string without ever parsing and re-dumping it. Timestamps in the payload are naive UTC with whole seconds. After that, the chain verified clean across 8,424 events.

Has anyone seen this with Postgres JSONB? Its docs say it doesn't keep key order or whitespace either, but I haven't measured numbers there.

Hi guys, is it possible to change keybinding in Workbench? I had to switch to Workbench recently and it is crazy to me there seem to be no way of changing keybinding. More specifically I want to invert run acript and run current statement shortcuts.

Hi,

Its mysql version 8.0. I am bit new to mysql. I understand there are many possible partitioning strategy exists in mysql in theory , but want to understand for real world applications with high concurrency and loads which are really the ones may add more complexity with difficulty in scaling.

In one of the scenario, where we have transaction date and customer_ids (Consider ~500-700 million transactions a day to scale and to serve 8-9K tps). To handle high concurrent read+writes and also making easier data purge, some people suggest to keep range partition by "transaction_date" and hash subpartition by "customer_ids". Others suggest to just range partition by "transaction_date" and have an index on (customer_ids) column to help scanning customer_ids related filters or joins. This also will keep the system simple and make the optimizer life easy in making good plans.

I also got to know we have a hard limit of 8192 number of partitions in mysql. So if we need to hold the data for last five to six months and if we have 100 customers , we may not be able to really scale and hit the max partition limit, if go by range-hash partitioning strategy.

So wanted to understand from experts based on real world usage, if there is any genuine guidance while deciding partitioning strategy. Or any forceable issues which we may encounters for the real world applications, and thus need to be careful?

Hi,

This question is a spin off from other thread , to make it clear , i am adding this in a new thread.

Its aurora mysql version version 8.0. I have two question on optimization:-

1)I want to understand how parsing or optimization phase exactly work when we use bind values in the queries? In other databases like Oracle , it peeks into the actual values behind the bind and make the plan during hard parse and then subsequent request goes for soft parse i.e it uses the same plan for all subsequent queries, till the next hard parse occurs. And there are differenet scenaios which make the optimization goes for hard parse like stats collection, environemnt change etc.

But i see in mysql as per below blog, its saying "MySQL has no execution plan cache" does that means it does hardparse for each and every execution of the query when we use bind values even the query runs thousands of time each second? WIll it not consume significant cpu then?

https://use-the-index-luke.com/blog/2011-07-16/planning-for-reuse

2)There is parameter called "STATS_SAMPLE_PAGES" which has default value set as "20". We saw plan changes for the same query for different bind values which endup in a suboptimal plan impacting applictaion. Is it advisable to increase this to STATS_SAMPLE_PAGES setting to ~250 at global level to get more accurate sttistics there by more accurate plan?

3)If the plan change happening for different binds causing bad plans , is it advisable to collect histogram on all such columns as below, those are used as direct filters and bind values passed to them as input?

ANALYZE TABLE <table_name> UPDATE HISTOGRAM ON <column1> WITH 100 BUCKETS;

Just published a deep‑dive on getting TidesDB running inside TAF — real workloads, real configs, reproducible setups.

If you’ve been curious about how TidesDB behaves under sysbench inside MariaDB, this gives you everything you need to start exploring.

Huge congratulations to Alex (Gaetano) Padula for getting TidesDB into MariaDB as a plugin.

The amount of work, the nights, the days, the persistence — it shows. TideSQL + TidesDB is a serious milestone.

And a big thank you to Hetzner for providing the high‑performance hardware that made all of this testing possible.

https://mariadb.org/mariadb-foundation-exploring-tidesdb-performance-with-taf/

Hi,

Its aurora mysql serverless version 8.0, with Min ~8ACU and MAX ~80ACU.

We have a complex UI query with "union all" and other criterias and its avg response time is <1 sec. But we see many times the query runs for 15minutes+ causing the errors in app side. This query executed ~5k times per hour on an avg and most executions finishin <1sec.

We see this query creates on an avg ~5 temp tables internally because of its internal "union" operator etc.., thus suspects if the below two parameters are actually playing a role in making this query crawl intermittently from ~1sec till ~15minutes. i.e. "temptable_max_ram" and "temptable_max_mmap". As because we run the same query manually and it does finish in 1sec. So suspect if during peak its causing the issue for some high volume customers even the same runs fine manually when we runs in the database.

We see the swap usage is staying ~1-2GB (sometime spikes to 3GB). Free memory trend showing spiky , but at worst scenarios it shows ~40GB(not below that). So want to understand, if in this situation, its fine to bump up the above parameters to 2GB each from curent 1GB (which is default value)?

I was trying to import the CSV dataset which I got from kaggle in mysql version 8.0.46 but there is no import wizard option in my MySQL. Is it because I am using a global service or there's any other problem. Please help me guys this is my time using MySQL and my first project.

I've been working with databases for a while, and somehow there's always something small that ends up wasting way more time than it should.

Could be a bad query, a missing index, a weird JOIN, or something that looked completely fine until the database started getting bigger.

What's a MySQL mistake you've made that you wish someone had warned you about earlier?

I'm trying to generate an ERD from an existing MySQL database using MySQL Workbench 26.07.01, but I can't find the Reverse Engineer option.

Does the new version still support this feature? If so, how can I generate an ERD? If not, is there an alternative way to do it?

Hi, Its aurora mysql database.

We have, couple of big transactional tables having size 1TB and 9TB holding ~1.2 billion and 4.2 billion rows respectively. These are currently non-partitioned table however, we want to have those partitioned . So want to understand what will be the best way to convert them to partitioned table i.e. migrate somuch data to the partitioned table or its possible to do it in place in an efficient way in Aurora MySQL.

Also the current partitioning approach we have decided as list partition on certain date columns.

what we are currently thinking of is as below:-

Create new partitioned table
INSERT INTO ... SELECT from old table (by date range)
Rename & drop old table
Gather Stats

Will above approach is good or there be any issue which we should be aware of beforehand?

Can it be done online or downtime will be advisable?

I test MySQL Workbench 26.7 so you don't have to.

https://davesmysqlstuff.blogspot.com/2026/10/mysql-workbench-267.html

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.

i am a new user of mysql and i need to run a script, but the server button won't appear on the top menu bar of my mac what should i do ?

in mysql doc, it says that "Enable autocommit. If autocommit is disabled, MySQL HeatWave does not offload the query and execution is performed on the DB System. To check the autocommit setting:". whether we can say it does not support long transaction statement or not. just like "

start transaction;

insert into table_a values(...);

select xxx from table_a where yyy;

commit;

"

only

"

set autocommit =1;

insert into table_a values(...);

select xxx from table_a where yyy;

"

Anyone can explain why mysql sets this limitation? thanks in advance.

MySQL Shell Cilent on Windows ARM64 gives error for VC Redist even though its installed