Last checked and updated on 13 Jun 2026
What Is New in MySQL 4.0
MySQL 4.0 introduced foundational features that significantly improved performance, query capabilities, and reliability for production systems.
| Category | Key Changes |
|---|---|
| New Features | InnoDB as default storage engine, Embedded Server library, Query Cache, UNION statements |
| Performance | Faster replication, Optimized nested-loop joins, Bulk INSERT speed improvements |
| Query Capabilities | Full-text search on InnoDB, Multi-table DELETE/UPDATE, Derived tables |
| Security & Reliability | New GRANT syntax, SSL support for replication, Faster CHECK TABLE and REPAIR TABLE |
How did query performance improve in 4.0?
The query cache was a game-changer, storing complete result sets in memory for identical subsequent queries. This made read-heavy applications much faster by avoiding repeated parsing and execution.
Nested-loop joins were optimized to perform better, and bulk INSERT operations saw substantial speed gains. In practice, this meant that complex reporting queries and data import jobs completed in a fraction of the time.
What new SQL syntax was supported?
MySQL 4.0 finally added support for UNION, a long-requested feature that allowed combining results from multiple SELECT statements. This was crucial for complex reporting that couldn't be done with a single query.
You could also perform multi-table DELETE and UPDATE operations, which simplified scripts that previously required multiple queries or transactions. Derived tables (subqueries in the FROM clause) also became available, offering more flexibility in writing queries.
Why was InnoDB integration important?
Making InnoDB the default storage engine was a major shift towards ACID compliance and transactional integrity. This mattered because it provided reliable commit, rollback, and crash-recovery capabilities out of the box.
InnoDB also brought row-level locking, which drastically improved concurrency for applications with high write volumes compared to MyISAM's table-level locking. Full-text search support on InnoDB tables started here, though it was more basic than MyISAM's implementation.
How did replication get faster?
Replication speed saw a significant boost through a more efficient mechanism for transferring the binary log from the master to slave servers. This reduced the latency for changes to propagate through the system.
The introduction of SSL support for replication connections was also critical. It allowed for secure data transfer over networks, which was essential for any deployment spanning data centers or untrusted networks.
FAQ
Should I use the Query Cache for my application?
It's highly effective for read-heavy workloads with repetitive queries, like many web applications. However, it uses a global lock, so it can become a bottleneck on systems with very high write volumes. Monitor the Qcache_hits and Qcache_lowmem_prunes status variables to tune its size.
Can I use UNION with ORDER BY and LIMIT?
Yes. You can use ORDER BY and LIMIT on the entire UNION result. You can also use them on individual SELECT statements within the UNION by wrapping them in parentheses. For example: (SELECT a FROM t1 ORDER BY a) UNION (SELECT b FROM t2 ORDER BY b);
Is the embedded server library production-ready?
It's designed for embedding MySQL into dedicated applications, like kiosks or packaged software, not for general web hosting. It links the server directly into your application, which can be powerful but requires careful management of resources and connections within a single process.
What are the advantages of multi-table DELETE?
It allows you to delete rows from multiple related tables in a single, atomic operation based on a join condition. This simplifies application logic and ensures referential integrity is maintained without writing procedural code to handle multiple DELETE statements.
How stable is the InnoDB full-text search?
In 4.0, it was a first implementation and lacked some features of the mature MyISAM full-text search, such as more advanced boolean operators. For most production uses requiring robust full-text capabilities, MyISAM was still the recommended choice at the time.