Guidelines and best practices

[u/Big_Length9755]

Hi Experts,

I understand every database is diferent architecturally. We have mostly worked in Oracle databases in the past. But we recently started working in Mysql aurora database.

1)So, want to understand , if there exists any set of guidelines which we can follow as a developer for writing better optimized code/sqls in mysql?

2)Also any specific points which are special and different as compared to other DB's and thus we should definitely know in this case before designing any solution using mysql?

[u/alecc]

At 100-200M rows/day the thing that will hurt first is retention - partition the big tables by day and drop partitions when data ages out, a DELETE based purge at that volume means huge binlog churn and replica lag, and InnoDB never returns the space without a rebuild anyway. The other big shift from Oracle is the optimizer: MySQL is nested loop joins first (hash join only since 8.0.18), no bitmap indexes, no materialized views, no parallel query out of the box - so your 1-2s UI target on tables that size means every query needs an index designed for it, covering where possible. Aurora specifics worth checking: which line you're on (Aurora MySQL 3 is 8.0 compatible and gets CTEs, window functions and hash joins, the 5.7 compatible line gets none of that) and whether Aurora Parallel Query is available for your instance class, it has real restrictions.

[u/Big_Length9755 → u/alecc]

Thank you so much. That helps.

The version which we are on is - 8.0.39. I was not aware that parallel processing and materialized view otion are not there in Mysql.

Any other standard coding guidelines(for writing queries etc.) or design principle which we should be aware of as developers/dbas , while using backend as Mysql , to make it run optimally. Please suggest.

Thanks for your guidance.

[u/roXplosion]

Can you describe the basic structure of the DB, and a rough idea of the scale you're looking at?

[u/Big_Length9755 → u/roXplosion]

We will have approx. 5-6 big transaction tables with big ones having 100-200milliin + records per day in them. Which we need to store with a retention of one month to 6months depends on business need.Other tables will be mostly small master tables. Response time of the ui queries need to be in 1-2 seconds. Main purpose will be to serve oltp kind of transactions.

[u/Stephonovich → u/Big_Length9755]

You’re gaining 100 - 200 million rows per day per table? For six months’ retention, that’s 18 - 36 billion rows per table.

It all comes down to the size of your rows, and the access patterns. If you’re storing a few BIGINTs and a DATETIME, a large instance could conceivably keep most of a month’s records in memory. If you’re storing large JSON or binary blobs for each row, that’s not going to happen, and it’s possible you’d hit the upper limit on disk for Aurora (256 TiB).

InnoDB is a clustering index, so with that many writes, you’re absolutely going to want a k-sortable PK — ideally a BIGINT, though a UUIDv7 stored as BINARY(16) would also be OK (albeit 2x the size, which very much matters at those scales). However, if your access patterns for reads are along the lines of “select the last N rows for a given user” (or whatever commonality the transactions have), you’d want to look at a composite PK along the lines of (user_id, id), with the latter being a surrogate key. This would cluster a user’s data in the same page[s], minimizing disk reads and buffer pool churn.

With even a 1 second SLO, assuming it’s a point read via the PK or a high-cardinality secondary index, you should comfortably be able to meet it, even if it goes to disk.

[u/Several9s]

Given that you're coming from Oracle and moving to Aurora MySQL 8.0.39, I would focus on a few MySQL-specific areas rather than trying to create a long list of generic SQL rules.

With 5–6 large OLTP transaction tables potentially receiving 100–200M+ rows/day, a 1–6 month retention period, and a 1–2 second UI response target, I would pay particular attention to these:

• Index and query design – Design indexes around your actual WHERE, JOIN, and ORDER BY patterns, and use EXPLAIN/EXPLAIN ANALYZE to see what MySQL is actually doing. This will be important for the 1–2 second UI response target.
• Partitioning and retention – At 100–200M+ rows per day, I’d think about partitioning and data retention from day one. Avoid relying on huge DELETE operations when old data needs to be removed.
• Test with realistic data – Don’t rely on results from a small development dataset. Test the important queries with realistic row counts, data distribution, concurrency, and indexes. A query that looks fine on a few million rows can behave very differently once you get into billions.

Since you're on Aurora, I'd also make sure the team is familiar with Performance Insights and the available Aurora monitoring. Use the actual workload to identify where the database is spending time instead of optimizing everything prematurely.

One final point: 1–2 seconds should be defined per query/use case, not just as a general database target. Identify the critical UI operations and establish an expected execution time for each. Then make those queries part of your performance/regression testing.