Why Oracle‑to‑MySQL Migrations Stall: Common Pitfalls and Fixes

Background

Customers often rewrite Oracle systems to MySQL and then face CPU usage at 100% or severe query backlogs during peak business hours. Similar issues were observed during large‑scale migrations such as Taobao’s early move from Oracle to MySQL 5.1, where sub‑queries and complex joins caused performance degradation.

Parallel Processing

Oracle can split large DML/DDL tasks into parallel subtasks, allowing simultaneous execution. MySQL lacks native parallel query support, so after migration any SQL that relied on Oracle’s parallel hints must be re‑engineered. Recommendations include:

Use Alibaba Cloud Analytic Database Service (ADS) to run parallel‑style analytical queries.

Decompose complex statements into multiple simple queries and perform the aggregation in the application layer.

Logical/Physical Reads and Execution Time

Oracle’s optimizer provides detailed metrics: logical reads (reads from buffer cache), physical reads (disk I/O), and total execution time. MySQL’s optimizer is less sophisticated, often resulting in higher logical/physical reads for the same workload. When logical reads or physical reads exceed 1,000,000 or execution time exceeds 5 seconds, consider the following:

Route heavy‑read queries to read‑only replicas or ADS.

Optimize indexes or rewrite queries to avoid full table scans.

Break multi‑table joins into separate statements and combine results in the application.

IN‑list Limitations

Oracle caps the number of items in an IN(...) clause at 1,000. MySQL has no hard limit, but the optimizer uses binary search and performance drops sharply as the list grows. Best practice is to keep the list under 100 items.

Subquery Performance

Before MySQL 5.6, subqueries were executed using a nested‑loop algorithm, causing severe bottlenecks on large tables. A typical problematic subquery:

SELECT first_name
FROM employees
WHERE emp_no IN (
    SELECT emp_no FROM salaries_2000 WHERE salary = 5000
);

Rewrite it as a join to eliminate the subquery:

SELECT e.first_name
FROM employees e
JOIN salaries_2000 s ON e.emp_no = s.emp_no
WHERE s.salary = 5000;

Upgrading to MySQL 5.6 or later also mitigates many subquery issues.

View Optimization

Oracle supports materialized views that store pre‑computed results and can be indexed. MySQL lacks materialized views, so migrating a view that required heavy computation may lead to slower queries. The recommended approach is to replace such views with application‑level caching or explicit tables that store the materialized data.

Function‑Based Indexes

Oracle allows indexes on expressions (e.g., DATE(gmt_create)). MySQL does not, causing full table scans when queries apply functions to indexed columns. Example problematic query:

SELECT * FROM emp WHERE DATE(gmt_create) = '2017-02-20';

Rewrite the query to use a range condition that can leverage a plain index:

SELECT * FROM emp
WHERE gmt_create >= '2017-02-20 00:00:00'
  AND gmt_create <  '2017-02-21 00:00:00';

Summary

MySQL does not support parallel queries; redesign or use external analytic services.

MySQL’s optimizer is weaker; monitor logical/physical reads and execution time, and apply index or query rewrites.

Keep IN(...) lists under 100 items to avoid performance loss.

Rewrite subqueries as joins or upgrade to MySQL 5.6+ for better handling.

Replace Oracle materialized views with application‑level materialization or explicit tables.

Eliminate function‑based indexes by rewriting predicates to use range scans on plain indexed columns.