I'll warn you right now, this is going to be a long article. More than likely, I'll put a version of this up on the MySQL developer zone and PHP zone. This article is intended to highlight various basic topics concerning proper methods of handling connections to MySQL databases in PHP, guidelines for caching dynamic content, and a technique called "lazy loading". Hopefully by the end of the article you'll have learned how to combat a very widespread and potentially devastating scalability problem seen in an enormous number of PHP web applications.

An introduction to the problem

Before I start the discussion on connecting to MySQL servers via PHP, it's worth pointing out that the relative cost of connecting to a MySQL database, versus connecting to a PostgreSQL or Oracle installation, is very, very low. However, the fact that connecting to a MySQL resource is inexpensive does not mean that connection resources can be abused. …

In an earlier article I explored the finer points of multi-table, cross-database deletes in MySQL, but I didn't get it quite right. This article explains the subtleties I've discovered since then. This is more complex than the MySQL manual documents.

MySQL will be hosting two separate one day European Customer Conferences in October.  This sounds like a long way away, but time moves quickly, so if you're in the UK, Germany or nearby, you may want to make plans to attend one of these events.  Think of these as one-day "mini-conferences" that jam the best of our technical presentations along with real world case studies and networking opportunities.  Last year's event in London sold out, so this year, make your plans early.

Topics to be covered include Detailed Technical Roadmap, High Availability Strategies, Using MySQL Cluster, Performance Tuning, Using MySQL in Web 2.0 applications and examples from customers and partners such as the BBC News, Neckermann, Unisys and more. 

• MySQL: …

Basics

Btree-Indexes must be less then 1024 bytes, so if you have a utf8 column that is 255 characters mySQL assumes 728 bytes, if you combine that with other varchar(255) utf8 columns you'll get an error.


Compound, composite indexes:

This term is used allot. It basically means 1 index across multiple columns.

For instance you have a table with

A tinyint
B tinyint
C tinyint
D tinyint

and a index on A,B,C taking 3 bytes.

following the principles of left-most-prefix key lookups (assume AND between columns)

A,B,C is an index lookup using 3 bytes
A,B order by C is a index Lookup taking 3 bytes 1 is used for a efficent sort.
A,B is an index lookup using 2 bytes
A is an index lookup using 1 byte

A, C is an index lookup using 1 byte. Notice that A,C does not use 2 …

utf8_bin collation on UNIQUE columns needs to be used with caution. In latin1

f == F so a duplicate error would be thrown if a user entered in 'F' when 'f' already existed.

With utf8_bin f != F so, make sure to normalize your data before sticking it into the dblayer i.e. put all email addresses, tags, etc in your db as lowercase :)

Thanks for reminding me Peter Z!!

Its a dirty job, but somebody's got to do it! I am talking about testing and debugging, of course, which is what I have been doing for the last month. But at least I have the satisfaction of knowing that XT is a lot more stable.

Over 95% of the "mysql-test-run" scripts now run through correctly with PBXT as the default engine. I have documented the changes made to the test scripts here: pbxt-test-run-changes.txt. This file also explains the major differences between MyISAM and PBXT, and lists all features are not yet implemented.

If you have compiled XT and would like to run the tests, then enter the following commands:

cd mysql-test
./mysql-test-run --force --mysqld=--default-storage-engine=pbxt

I have also compiled and tested XT on a multi-processor, 64-bit machine. I used …

So I got a question from a developer today who was trying to SELECT NULL values from a table. As I have been asked about this many times in the past, I decided to write a little post about it.

mysql> SHOW CREATE TABLE testing_null \G
*************************** 1. row ***************************
Table: testing_null
Create Table: CREATE TABLE `testing_null` (
`id` int(11) unsigned NOT NULL auto_increment,
`mycolumn` varchar(12) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO testing_null (mycolumn) VALUES('ho'), (NULL), (NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testing_null;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho | …

Should you want to be on the bleeding edge, or in my case, don’t want to download 70MB each day in a daily snapshot (especially when I’m getting build errors), you can use Bit Keeper Free Bit Keeper Client that at least lets you download the MySQL Repository. This client doesn’t allow commits, which is a good thing for those non-gurus in mysql internals (which definitely includes me).

wget http://www.bitmover.com/bk-client.shar
/bin/sh bk-client.shar
cd bk_client-1.1
make

By placing sfioball in your path you can execute.

sfioball bk://mysql.bkbits.net/mysql-5.1 mysql-5.1

This took me about 4 mins, which seemed much quicker then getting a snapshot!

You can then get cracking with my instructions at Compiling MySQL Tutorial 1 - The Baseline.

A good reference …

I'm building a MySQL Cluster which you can see pictured on the right. Partly it is for serious learning and testing purposes, and partly because it is winter Down Under and this keeps the office warm. The smallest desktop box goes particularly well under the desk as a heated foot rest :-)

• Management Node: PIII 700 Dell Laptop mal (My old faithful laptop.)
• SQL Nodes: 2 x PIII 800 Dell Optiplexes inara, river (Optiplexii? Different form factor, doh! How messy. Had these two boxes laying around the office already.)
• Data Nodes: 4 x PIII 1GHz clone boxes w/ 512Mb RAM simon, jayne, book, wash (Found these boxes cheap at an ex-gov computer market. Another 1Gb RAM each on order - how rare SDRAM is becoming - for some DataMemory breathing space.)

I have several …