Classic.
If you are so sure that there are Java DB products out there that can
run circles around MySQL, then why the hell are you on this list
begging for improvements to MySQL?
We all know the answer.
> 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.
