Ben Sampson has a tutorial posted to his site for the Laravel users out there sharing some tips about speeding up your database queries when using relationships between models.

Adding indexes to your database tables is a great way to get some extra performance out of your application, especially if you have a large amount of data in your tables. They should be used sparingly and only on identified slow queries, as they have implications of their own such as increased table size and increased RAM usage. But those potential drawbacks are well worth it when you can get a query down from a 3 seconds 15 milliseconds with 5 minutes of work. The effects are particularly noticeable on polymorphic / many to many polymorphic relationships.

He then includes the code required to create the indexes on your tables (either a single column or a compound index involving more than one column) in your migrations. He also provides code examples showing how to use foreign keys to improve one-to-one/one-to-many relationships. More examples include optimizations for many-to-many relationships and polymorphic relationships.

Lorna Mitchell has a new post today spotlighting a handy feature of the phpMyAdmin tool - the visual designer view. It allows you to build (and see) the relationships between your MySQL-based application's elements.

I'm happier at the command line, but needed some graphical representation of information and easy ways to export example queries for the book I'm working on. I noticed that phpMyAdmin now has a Designer tab, which shows relationships between tables and allows you to define them.

She includes two screenshots of the process of setting up a foreign key on two tables. You can find lots more articles related to the designer view here.

On the Planet SoC blog today, there's this look at how ifehhali added foreign key support to the MDB2_Schema_Writer as a part of his Summer of Code project to improve the PEAR::MDB2_Schema package.

MDB2_Schema_Writer is the responsible for dumping a database schema to a XML file. It walks into a database definition and outputs the correspondent XML tags.

He gives an example of checking a table (in $table) to see if it has constraints and check its validity. If everything's okay, the script pushes data into a buffer of specially formatted XML data.

On the Planet SoC blog today, there's this look at how ifehhali added foreign key support to the MDB2_Schema_Writer as a part of his Summer of Code project to improve the PEAR::MDB2_Schema package.

MDB2_Schema_Writer is the responsible for dumping a database schema to a XML file. It walks into a database definition and outputs the correspondent XML tags.

He gives an example of checking a table (in $table) to see if it has constraints and check its validity. If everything's okay, the script pushes data into a buffer of specially formatted XML data.