(mysql.info) portability

 
 7.1.2 Designing Applications for Portability
 --------------------------------------------
 
 Because all SQL servers implement different parts of standard SQL, it
 takes work to write portable database applications. It is very easy to
 achieve portability for very simple selects and inserts, but becomes
 more difficult the more capabilities you require. If you want an
 application that is fast with many database systems, it becomes even
 more difficult.
 
 All database systems have some weak points. That is, they have
 different design compromises that lead to different behavior.
 
 To make a complex application portable, you need to determine which SQL
 servers it must work with, and then determine what features those
 servers support. You can use the MySQL `crash-me' program to find
 functions, types, and limits that you can use with a selection of
 database servers. `crash-me' does not check for every possible feature,
 but it is still reasonably comprehensive, performing about 450 tests.
 An example of the type of information `crash-me' can provide is that you
 should not use column names that are longer than 18 characters if you
 want to be able to use Informix or DB2.
 
 The `crash-me' program and the MySQL benchmarks are all very database
 independent. By taking a look at how they are written, you can get a
 feeling for what you must do to make your own applications database
 independent. The programs can be found in the `sql-bench' directory of
 MySQL source distributions. They are written in Perl and use the DBI
 database interface. Use of DBI in itself solves part of the portability
 problem because it provides database-independent access methods. See
  mysql-benchmarks.
 
 If you strive for database independence, you need to get a good feeling
 for each SQL server's bottlenecks. For example, MySQL is very fast in
 retrieving and updating rows for `MyISAM' tables, but has a problem in
 mixing slow readers and writers on the same table. Oracle, on the other
 hand, has a big problem when you try to access rows that you have
 recently updated (until they are flushed to disk).  Transactional
 database systems in general are not very good at generating summary
 tables from log tables, because in this case row locking is almost
 useless.
 
 To make your application _really_ database independent, you should
 define an easily extendable interface through which you manipulate your
 data. For example, C++ is available on most systems, so it makes sense
 to use a C++ class-based interface to the databases.
 
 If you use some feature that is specific to a given database system
 (such as the `REPLACE' statement, which is specific to MySQL), you
 should implement the same feature for other SQL servers by coding an
 alternative method. Although the alternative might be slower, it
 enables the other servers to perform the same tasks.
 
 With MySQL, you can use the `/*! */' syntax to add MySQL-specific
 keywords to a statement. The code inside `/* */' is treated as a
 comment (and ignored) by most other SQL servers. For information about
 writing comments, see  comments.
 
 If high performance is more important than exactness, as for some Web
 applications, it is possible to create an application layer that caches
 all results to give you even higher performance. By letting old results
 expire after a while, you can keep the cache reasonably fresh. This
 provides a method to handle high load spikes, in which case you can
 dynamically increase the cache size and set the expiration timeout
 higher until things get back to normal.
 
 In this case, the table creation information should contain information
 about the initial cache size and how often the table should normally be
 refreshed.
 
 An attractive alternative to implementing an application cache is to
 use the MySQL query cache. By enabling the query cache, the server
 handles the details of determining whether a query result can be
 reused. This simplifies your application. See  query-cache.
Info Catalog
(mysql.info) design-limitations
(mysql.info) optimize-overview
(mysql.info) internal-use
automatically generated byinfo2html