MySQL - a popular Open Source database

Wow... having proper indexes in MySQL sure makes a big difference!!

A database at work (see PHPRant) had one really slow page that uses a relatively small amount of data (couple thousand rows in 2 tables), but is the main page used by the users. Turns out that MySQL did a very poor job on that. Adding an new index on only table2(table1_id) really helped. A key thing to remember is that MySQL will only the left most fields in an index. So if the index was previously on table2(table2_id, desc, table1_id) it would get totally ignored.

This was a simple fix, but took probably 2 days of combined debugging and wasted hours of user productivity. 60 seconds a page load vs. < 1 sec really adds up... --Wim

Ughh, this is so ugly:

The way columns that are to set to NOT NULL are handled is just plain bad. Business rules should be enforced by the database, not the application. The way MySQL implements them, if you have a perl script that's say bulk inserting 1000 rows, and the 500th one has no "first name" defined, then it will just insert a blank value if that column is set to not null in the database. No warnings or errors are giving. Ughh.

See also: Databases