-----Original Message----- From: Dave Morse [mailto:dave...@attbi.com] On Behalf Of dmo...@attbi.com Sent: 12 July 2002 15:57 To: ja...@lists.mysql.com Subject: MySQL performance

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.

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail java...@lists.mysql.com instead.