RE: MySQL performance

[Jon Frisby]

> ---Prepared Statements
MySQL has much less overhead in terms of parsing SQL statements, so there is
far less need for prepared statements and the performance advantage of
databases with prepared statements is thus relatively minor.

As for bulk INSERTs, MySQL has a bulk INSERT syntax that allows for the
quick and efficient loading of large chunks of data with a single INSERT
statement.  Do a mysqldump --opt using MySQL 4.0.x -- it produces SQL that
disables KEY checking temporarily, and performs *1* INSERT for each table.


> ---Subqueries
> Each option has its own extra overhead.
Actually, JOINs are often far more efficient than sub-queries as you can
eliminate rows from the end result set much earlier on and without having to
actually access the row (use just indexes).  But yes, the lack of
sub-SELECTs is often both annoying and frustrating from both a performance
and development standpoint.


> 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).
MySQL has a multi-table DELETE and an (experimental) multi-table UPDATE
syntax that allows you to JOIN multiple tables for use in determining which
rows are deleted from one or more of the JOINed tables:

DELETE foo, bar FROM foo, bar, baz WHERE foo.id = bar.id AND bar.id = baz.id
AND baz.whatever = 3;

This would delete rows from the foo and bar tables, using information from
the baz table to determine which rows to remove.


> 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.

And as soon as your data set is too large to fit in memory, any benefit
falls away completely and you have the overhead of having your IMDB act as a
proxy to your physical DB.


-JF