I usually work with MySQL although I have some experience with SQL Server. Sometimes I had the chance to work with Oracle, Access and PostgreSQL. Since PHP doesn’t provide a DB abstraction layer [1], I am using ADODB to make my life easier.

ADODB offers a lot of features beyond simple abstraction e.g. a performance monitor. I highly recommend it.

For various reasons I also use my own simple layer on top of ADODB. I call it DBI (DataBaseInterface) and it provides a) a reassurance that if for some reason I want to get rid of ADODB I can do so easily and b) extra features e.g. an audit trail for each field, row and table.

Recently I wanted to see the performance hit I am taking because of these layers. So I did some simple benchmarks. I basically executed a select query (returning ~350 rows about 1 kbyte each) multiple times (2500-5000). Keep in mind that both layers are written in PHP [2] and I am not using an accelerator.

DBI, ADODB, MYSQL

Returned rows are loaded in an array.

DBI: DBI->ADODB->mysql* functions
ADODB: ADODB->mysql* functions
MYSQL: Just mysql* functions

DBI     100%
ADODB    93%
MYSQL    67%

There is not a significant difference between ADODB and DBI. This is expected since DBI is a simple class around ADODB. However the difference between DBI and MYSQL is certainly significant.

FETCH MODE

Returned rows are loaded in an array.

I also compared ASSOC, NUM and BOTH fetch modes.

ASSOC    82%
NUM      72%
BOTH    100%

The NUM and ASSOC modes are close (the PHP manual agrees with that) but the BOTH mode is more expensive, which seems normal (since twice the amount of data is returned). There is no reason to use the BOTH mode, which by the way is the default mode for mysql_fetch_array().

Conclusion: there is certainly a performance hit when using database layers [3]. On the other hand the extra features are useful and since most applications don’t have special performance requirements I’ll certainly continue to use them.

[1] PHP 5.1 and later provides PDO, but I haven’t tried it.
[2] ADODB also provides its functionality as an extension.
[3] Also see some older benchmarks from ADODB creator.

Recently I had to use Windows-1253 character set with MySQL. Unfortunately it is not included with MySQL and I couldn’t find it anywhere.

So I started creating it with a fellow developer. However it wasn’t that easy. Maybe we should have studied the documentation more carefully.

Today I tried again and this time I was successful. Download Windows-1253 character set for MySQL.