MySQL performance

[<dmorse]

Rather than continue the debate about the efficacy of benchmarks versus
real-world applications, I would like to examine MySQL's limited SQL
support and how it impacts performance. These are areas where a
full-featured Java DBMS can truly 'run rings around' MySQL in
performance.

---Prepared Statements

MySQL does not have native support for Prepared Statements. With the
current version, you can't prepare a SQL command once (compile syntax
and create a query plan on the server) and then execute it multiple
time, optionally changing scalar parameters each time. Prepared
statements must be emulated for MySQL by preparing the SQL again for
each execute. This adds significant overhead.

The performance advantage of a full DBMS is most startling for bulk
insert operations, however prepared statements are also used heavily by
applications for updates, deletes and queries. Illustrative of this are
advanced connection pooling software packages that also pool prepared
statements.

Professionally developed applications use prepared statements most of
the time (when they're not using stored procedures.)

---Subqueries

The lack of subquery support in MySQL has significant impact on
performance. Subqueries can be emulated in several ways,

+  using joins, in some cases.

+  creating a temporary result table for a subquery and then joining to
the temporary table.

+  application logic, for instance storing subquery results in program
memory.

Each option has its own extra overhead.

Much of the focus on subqueries has been their use in queries, but they
are also vital for Update and Delete commands. Without subqueries,
Updates and Deletes cannot be based on the contents of other tables. For
example, it is not possible to delete customers without associated
orders using a single Delete command. In addition, updates and deletes
using subqueries can only be emulated using application logic (the 3rd
option listed above).

---Main-memory processing

Now MySQL v4 can take advantage of IMDB technology providing a 4 times
increase in some processing but still a significant advantage over
traditional disk-based DBMS - but staying even with Oracle in the Feb.
25 EWeekLabs report.  Benchmark results show IMDB architecture makes
FirstSQL/J TPS performance ten times (10x) FASTER than the disk-based
version, which was already faster then MySQL v3.23.

Peace,

Dave M.