Robert's Blog

symfony 1.3 Book by Tim Bowler & Wojciech Bancer

Symfony 1.3 Web Application Development is the latest book on the symfony PHP web framework and the first not written by the authors of the framework.

This allows the authors to adopt a more pragmatic style that favors 3rd party plugins.  This is a good thing because IMHO the most important thing about a framework is it’s library of extensions.

The book also has a nice blend of information available in the already available symfony book, cookbook, and book length tutorials using the techniques that a actual development team would use.

Unfortunately this book was pushed out before symfony 1.3 was finished, and apparently before a sufficiently detail oriented proofer could go through the book.  Luckily the issues are primarily non-technical and mostly limited to the first few chapters and should not deter you from purchasing the book.

The most notable technical issue I have with the book is the use of a XML schema instead of a YAML one, this actually works fine, it’s just a non-standard way of doing it.  Also sometimes it feels like the authors did a search for 1.2 and replaced it with 1.3, again this is not that big a deal because the differences between the versions are mostly not relevant to beginner or intermediate symfony developers.

So would I recommend this book?  Yes, especially if PAKT comes out with a second revision with better editing.  This book provides valuable real world examples and accessible detail that eases the difficult learning curve of symfony development.

Internationalized (i18n) Admin Generator CRUD’s in Symfony 1.2.9 + Doctrine

I was having some trouble finding documentation on how to i18n generated CRUD’s, so once I figured (most) of it out I thought I’d share it

The Example Application

Since I have to create a feature in my one of my current work projects to store random bits of content, like privacy policies and such, in multiple languages. I thought I’d double dip and use that for this example.  I’m calling the feature content blocks.  It will have a backend CRUD that will facilitate translations.  The UI I needed was to have the default language show up as well as one of the many languages this information would be translated into.  My app has the possibility of having more than 20 language options so putting them all in the CRUD at once was unreasonable.

The example to the left shows what the CRUD looks like when French is set as the user’s culture.  If the default language is chosen a second language form does not show up.

On the frontend I’m just going to do a simple data pull for this example.  Both the frontend and the backend app will have very simple language switchers  to demonstrate how that works.
Now that I now how this works it’s actually pretty darn simple, however figuring it out took longer than I’d like.  Hopefully this tutorial will save you some time.

I’m going to skip the application setup, if you don’t know how to do that I used the same steps that are in the Doctrine version of the Jobeet tutorial.

Download the example app zip file here, includes Symfony 1.2.9

Doctrine schema.yml

The database schema.yml was by a little tricky at first.  I did not realize that Doctrine handles I18n tables so much differently from Propel.  With Propel I would have defined a second table named content_block_i18n and put the translated fields there.  For Doctrine they simply go in under the actAs and I18n.  This is less typing and I suspect more intuitive for those who don’t already know Propel.

Also remember to put columns: before your field definitions, and leave out the connection at the top of the file.  Timestammable adds the created_at & updated_at fields.  Also notice that the data types are different from Propel’s.

I think I’m going to like these changes, but they are different so be careful if you are used to Propel.

content_block:
actAs:
Timestampable: ~
I18n:
fields: [short_title, title, extract, content]
columns:
weight: integer
active: boolean
short_title: string(50)
title: string
extract: string
content: string(4000)

Once you’re done: create your database, edit databases.yml, build-all, and clear you cache.  Details are in the Jobeet tutoral in Day 3: The Data Model.

Adding embedI18n() to the form class a.k.a: where the magic happens

This part took some serious research, I was just sure all I had to do was edit something in the generator.yml, but that turned out not to be the case.

I finally found  embedI18n() in the Forms in Action book in the i18n chapter under Propel Objects Internationalization.  It does use the much maligned sfContext, and if you know a better way write a comment.

You’ll want to generate forms(php symfony doctrine:generate-forms) & then the contend block CRUD (php symfony doctrine:generate-admin backend ContentBlock), as well as turn I18n on in the backend settings.yml.

This is in: lib/form/content_blockForm.class in the example application.

/**
* content_block form.
*
* @package form
* @subpackage content_block
*/
class content_blockForm extends Basecontent_blockForm
{
/**
* Form configuration settings
*
* @author Robert H. Speer
*/
public function configure()
{
$this->embedI18n(array(sfConfig::get('sf_default_culture', 'en'),
$this->getCurrentCulture())
);
}


/**
* pulls the current culture from the user object
*
* @return string
* @author Robert H. Speer
*
* Notes:
* RHS 10/2/09 - sfContext::getInstance() violates MVC but I don't know a way
* around it ATM.
*/
public function getCurrentCulture()
{
$culture = sfContext::getInstance()->getUser()->getCulture();

if (strlen($culture)>0) { // return user selected language
return $culture;
}else{ // return default culture, or defaults to english
return sfConfig::get('sf_default_culture', 'en');
}
}

}

A simple language switcher component

I’ve included a very simple language switching component in the example application.  Assuming you know how to write a component, it’s not a big deal.

The actual language setter is in both apps (i know wet is bad) under language_switcher/actions/action.class.php & looks like this:

/**
* changes the users culture and redirects them back the their previous page
*
* @author Robert H. Speer
*/
public function executeLanguage() {
$this->getUser()->setCulture($this->getRequestParameter('culture'));

$url = $this->getRequest()->getReferer() != '' ? $this->getRequest()->getReferer() : '@homepage';
$this->redirect($url);
}

It’s going to take the user’s selected culture set that to the user object, and then redirect causing a refresh.

How to get at that translated content

This is the easy part, you actually don’t have to do anything special to grab content in the language set in the user object, just get the object and call the getter.

Grab the object(s) with something like this, but preferably in the model layer instead of apps/frontend/homepage/actions/action.class.php:

/**
* Executes index action
*
* @param sfRequest $request A request object
*/
public function executeIndex(sfWebRequest $request)
{
$this->block = Doctrine::getTable('content_block')->createQuery('a')->execute();
}

Then in your template you can access all the fields just like if they were in the same table (this is really cool):

/**
* Very simple homepage for demo purposes only
*
* @author Robert H. Speer
*/
foreach ($block as $key=>$row)
{
echo 'id: '.$row->getId().'<br>';
echo 'weight: '.$row->getWeight().'<br>';
echo 'short title: '.$row->getShortTitle().'<br>';
echo 'title: '.$row->getTitle().'<br>';
echo 'extract: '.$row->getExtract().'<br>';
echo 'content: '.$row->getContent().'<br>';
echo 'created at: '.$row->getCreatedAt().'<br>';
echo 'updated at: '.$row->getUpdatedAt().'<br>';
echo 'lang: '.$row->getLang().'<br>';
echo '<hr>';
}

If you get the example app going on your own machine add a few records with some translations, then change the language with the language drop down and it will just work automagicaly.  For your own apps remember to turn I18n on in your applications settings.yml.

Reference Links

• As always the Symfony’s sweet documentation
• Sandbox Web Solutions does a great job of explaining a different way to embed i18n forms in a generated CRUD

What I have not figured out yet

• I have got the file upload widget to show up in the Admin generator but I it does not work automagicaly, like I think it should, I think I’m going to have to write the file handler myself.
  • edit 10/04/09 10:42PM -> Stereo Interactive has a work around for this
• Getting at the embedded fields in the generator.yml is elusive as well.

I’ll be working on both of these problems as soon as I get back to work, so hopefully I’ll have an update soon.  If you figure it out first please write a comment.

Disclaimer:

By the time I was done writing this tutorial I was very ready to not be at my computer anymore, there are going to be some grammatical mistakes and maybe some code ones as well.  I through this together on WAMP, on my home desktop, so you may have to change the slashes on your path, and update your apache conf &/or your .htaccess file to get it to work.  The application I’ve uploaded does work, but it is just a demo so don’t trust it too much

Symfony Plugin Review: sfEasyGMapPlugin

Symfony Plugin Review: sfEasyGMapPlugin

The sfEasyGmapPlugin seeks to encapsulate much many of the features in the Google Maps API and make them easily available in the Symfony PHP 5 Framework.

The Test:

I set up a very basic Symfony 1.2.9 project and installed the plugin.  Installation of the plugin was a breeze the command line install worked flawlesly. Which is very nice, and unexpected, as many plugins work better if you install them manually.

The sample module worked exactly as advertised, after I corrected my Google Maps API key.

Pretty soon I had an example module created and was inputting my own locations, geocoding addresses, re centering, and using some nice syntactic sugar like the centerAndZoomOnMarkers method that does exactly what it says it does.

The example module covers the basics, but I’d also encourage you to browse through the classes to get a sense of that this plugin will do.

My testing was fairly simplistic and I don’t have any experience with this app on a large application, however it would be my first choice if I had a project requiring mapping.

The Results:

The code is well done, object oriented, intuitive, & very easy to use.

My only criticism is that it could do with a little more content in it’s PHP docs, but that’s pretty minor.

I would strongly reccommend this plugin, and look forward to an excuse to use it, and I’d like to thank the authors for providing the community with such great code.

Thanks to:

• Fabrice Bernhard
• Laurent Bachelier
• Tom Haskins-Vaughan
• Vincent Guillon

The authors come mostly from Allomatch

Symfony refactor of the Zend Quick Start Tutorial

Learning Zend Framework and getting a repetitive stress injury doing it

Edit: please see Matthew Weier O’Phinney’s (the current project lead for Zend Framework) response in the comments, there are some exciting things comming for ZF

My friends & colleagues have used Zend Framework (ZF) for a while, and I do my best to avoid it and use the Symfony PHP framework.  Initially I was open to learning ZF, I was just curious why people liked it.  The more questions I asked, the more I realized there were no good answers other than standards for standards sake, and variations on the Sunk Cost Fallacy.  If pressed I was told that I had to give Zend Framework a chance because it is a younger framework than Symfony, (um no).  Some of the developers had even written a library to add on to Zend Framework to make it more usable, it contained features that were already in Symfony.  IMHO, writing code to help a framework catch up is an excellent reason to switch to another framework.

This last week I was finally forced to use ZF, my rebellious use of Symfony only served to annoy the other developers, and had the potential to increase maintenance costs.  It seemed like a reasonable request, and all those developers couldn’t be wrong could they?

Well yes they are.  I work with some great people whose feelings I don’t wish to hurt, but I would estimate that Zend Framework projects cost between 20 to 40 hours more for projects that run around ~400 hour plus. With agency rates what they are that could turn in to 6 or 7 thousand dollars of added costs. Assuming requirements for user authentication and backend CRUD’s.  These numbers will vary widely from project to project.

From a quick comparison of the Symfony Case Studies and the Zend Framework Case studies I would expect this efficiency gap to continue to increase.  A couple of Zend Framework’s case study subjects have already failed: QEDWiki & PokerRoom.com, and anyone who’s been unfortunate enough to have to customize Magento likely regrets the experience.   Symfony’s case studies are large successful projects you may have heard of:  DailyMotion, Yahoo! Answers, Delicious, & Yahoo! Bookmarks.  To be fair I’ve seen some really nice Zend Framework apps, I’ve just seen more interesting and larger Symfony projects.

A Quick Comparison

To present as fair a comparison as possible I have rewritten the Zend Framework Quick Start as a Symfony app. I also used Symfony 1.2 with the Doctrine ORM, both of which I’ve not used before, as my projects have been in Symfony 1 or 1.1 and the Propel ORM so far.

I’ve formatted this more as a timeline of events rather than a tutorial, however the full Symfony app source is available HERE.  To drop it on your web server you will have to change the path to Symfony in config/ProjectConfiguration.class.php, I had it running through WAMP on my local machine.

This also serves to document how I wasted a perfectly wonderful summer morning, *sigh*.

I did change some things around, I used MySQL because I’m not familiar with sqLite and, I used reCaptcha instead of a captcha similar to the tutorials.  I think these add difficulty, and are fair changes.

1. 9:15am – 9:30am set up symfony app
2. 9:30am -9:38am skip a bunch of Zend configs and translating layout to sf
3. 9:40am – 9:43am generated controller & view, skipping autoloading in the bootstrap file
4. 9:43am – 10:06am set up database
   1. wrote schema.yml to define guestbook table
   1. setup database connection string:
      1. php symfony configure:database –name=doctrine –class=sfDoctrineDatabase “mysql:host=localhost;dbname=guestbook” username password
   1. 10:06am – 10:20am building model
      1. php symfony doctrine:build-model (Getters, Setters & Stuff or GSS)
      2. php symfony doctrine:build-sql   (generates the sql)
      3. php symfony doctrine:insert-sql  (inserts the sql into the db)
      4. skipping the build database script, symfony does that for us
5. 10:20am – 10:35am Listing out all guestbook entries in view  (got distracted with doctrine, I usually use propel)
6. 10:48 am Generating form classes
   1. configuring to remove created at from view
      1. unset( $this['created_at'], $this['updated_at'] );
7. random interruptions by wife….
8. 11 am writing view layer
9. 11:15am writing controller for saving
10. Setting up captcha
    1. installing form extra plugin
       1. php symfony plugin:install sfFormExtraPlugin
       2. I can’t type, kept putting the api keys in wrong , fancy reCaPTCHA complete (11:51am)
11. Double checked ZF Quick Start to make sure I did not forget anything
12. 11:54am done

Development Highlights:

Symfony:

• under 150 lines of me-written code, including html I C&P’d from the zend tutorial
  • this means much less fumble finger type mistakes
• ~2hrs 39 min to complete from httpd.conf setup to form submission.
  • with distractions, a mySQL db, and a reCaptcha
  • I’ve clearly spent more time complaining about Zend Framework than it would take to complete the mini app in Symfony
• The only config files I touched were
  • config/ProjectConfiguration.class.php to use Doctrine instead of propel auto-magically
  • config/doctrine/schema.yml to define the db tables
  • config databases.yml was set up from the command line so you be the judge on that one

Zend:

• Configuration done in:
  • Bootstrap file configuration
  • appliation.ini configuration
• the zf tutorial expects you to write around 515 lines of code to do the same thing as Symfony
  • that’s from the tutorial text not the well commented source code.
• Also there promise of a “30-minute tour” can’t mean that you can program it in 30 minutes

Conclusions

Zend Framework is not bad, compared to using plain PHP there are some significant efficiency gains to be made.  However, when compared with Symfony and other frameworks, like Django & Rails,  it’s missing key features found in modern Web Development frameworks.  The tutorial I described here demonstrated the efficiency issues of not having code generation for the Model layer.  Two other key features are generated CRUD’s for backend site management, and a full MVC plugins like Symfony, Rails, & Django all have.

The long term ramifications of not having plugins and generated code accelerating your project are corners cut on quality, reduced features, scaling problems, and less competitive bids.  The lack of robust plugins in Zend also means that it will never be able have as many features as frameworks that do have Plugins.  Not having code generation means that developers are spending too much time writing mindless getter’s, setter’s, & data grids and not enough time focusing on the core features of the project, or worse they are making compromises in quality to make deadlines.

I don’t believe that the Zend Framework is so far behind that it can’t catch up.  In certain areas it’s actually ahead of the game, however those areas tend to not be fascinating edge cases I get to use every so often, and not features I use on every site every day.  My suggestion for Zend is use the Model layer examples available (Active Record,  SQLAlchemy, Doctrine) and  do something like that.  Doctrine already integrates well with Zend, maybe that would be a good option.  Then start generating admin interfaces or CRUD’s, this is huge, I find all kinds of ways to use these things to add value to my projects with a little typing on the command line.  Finally Plugins, the most important consideration when reviewing a framework or CMS.  The quantity and quality of plugins demonstrates the quality of the development tool.  It also means that there are developers out there that care about the tool outside of the core team.

If you are interested in learning more about Symfony there are excellent tutorials like the Jobeet example app, a cookbook, a guide book, a reference book, an API, and more on the symfony doc’s page.

What Three Twin Cities Web Entrepreneurs have in common

The EstateSales.net story is similar to the other successful small to medium sized start-up stories I’ve heard from other Internet start-ups.  

• Rob Buntz  has a story where he was involved in real estate as a investor, then a close relative needed to buy a house and didn’t want to pay a huge commission to a agent, and Webdigs.com was born.
• Charles Bailey   told me a similar story on how he founded ResortsandLodges.com and went on to expand, through acquisitions and growth into TravelNet Solutions. He found a problem he solved the problem, he didn’t mess it up.
• Daren Cotter this guy…ugh amazingly successful with all kinds of growth awards. Apparently Daren got involved in some early attempts at online customer loyalty sites decided he could do better, and did.  

What all these guys did was get out and participate in the community around them, pay attention, and find a problem with a magnetizable solution that could be repeated over and over, then they didn’t mess it up.  It’s not rocket science, it’s not fancy or glamorous, but it is lucrative & rewarding.

One importance difference between the 3 of these companies is that Webdigs.com has not found an effective way of communicating what they are about in 5 seconds.  In more traditional sales, a pitch is limited to a 30 second elevator speech.  However on websites I think that users only allow for more of a 5 second pick-up line.  Both TravelNet Solutions & CotterWeb do a better job of communicating to users almost instantly why their sites are compelling.

EstateSales.net – A great example of a niche market website

online bookstore optimized for “muddling through”

http://zoomii.com

This is a really interesting take on a online bookstore that is really optimized for free form exploration with a very intuitive interface for those accoustomed to a mouse with a scroll wheel.

Interesting User Interface, and it brings up some thought provoking usability questions.

This type of experimentation is going to lead to some very interesting Information architecture in the future.

Free Geek – Help the needy get nerdy in the Twin Cities!

“FREE GEEK is a 501(c)(3) not for profit community organization that recycles used technology to provide computers, education, internet access and job skills training to those in need in exchange for community service.”

There are a couple of folks looking to set up a Free Geek branch in the in the the Twin Cities

If you are interested in helping out you can contact them here.

Here’s a video with the low down on why this organization is so cool:

Webdigs got a 8.5 rating on it’s mortgage calculator

I don’t know why I’m still surprised by the Internet, but there’s a guy who ranks loan calculators.

Fortunately he liked webdigs.com’s mortgage calculator:

http://superloancalculators.com/?p=12

A new version of the mortgage calculator will be out soon, maybe we’ll break 9.

Webdigs.com, (with symfony inside) recognized as “kick ass” by Future of Real Estate Marketing

Future of Real Estate Marketing, a pretty awesome real estate blog, listed Webdigs.com as one of 10 “kick ass” real estate search sites.

This project would have been MUCH more difficult without Symfony providing structure, keeping the teams code somewhat consistent, taking care of so much drudgery.

http://www.futureofrealestatemarketing.com/10-kick-ass-real-estate-search-sites/

I got to admit, this was pretty cool, but not as cool as this

Twin Cities Real Estate is a Investors market

I’m not an agent, but I have been working on webigs.com for over a year now as a Developer. From what I’ve seen I feel fairly comfortable shooting my mouth (blog) off saying that the Twin Cities is not a Buyer’s market, or a real Seller’s Market.

I say this because the repo houses are dragging the aggregate numbers down and scaring sellers into lower prices, that are probably more in tune with prevailing wages. At the same time the Repo twisted numbers are causing buyers to make low ball offers and / or be unnecessarily nervous because of the uncertainty in the market.

From the data I see, and my experiences in multiple failed attempts at purchasing a home, decent well built houses at a fair price will sell fairly quickly to people who intend to live in the house.

Where the market gets really interesting is the opportunity to invest in real estate. Right now I think a lot of money has been scared out of the market, and the inventory of Bank Owned houses that that will take some work and 3 months of messing around is up. These barriers to entry are not unacceptable to someone who has bought and sold residential properties before. The upsides are the lower prices for Bank or Corporate owned prices and the good market for good houses.

My advice, for what little it’s worth, is if you’re an investor now is the time to put money into Real Estate. If you’re a home buyer, don’t think you’re going to get some amazing deal, but expect a fair deal. If your selling well the times are not as outstanding as they were, but they’re still pretty darn good.

Whatever you do use Webdigs.com and get 2/3 of the buyers agent commission back, or if your selling a easy flat fee of $2000. It’s hard to screw up when you’re saving that much to start with and you have a whole team of experienced agents working with you the whole way.

TableKit a easy sortable table based on Prototype.js

Many times using scripts I find on the Internet turns into kind of a hassle. They are usually unfinished side projects, or are kind of bloated and slow.

TableKit is not one of those scripts, it’s fast & easy to implement. Development time was low, and the designers didn’t complain too much about working with it. What it lets you do is create a html table slap in some ID’s & classes, load the js, and you have a really nice sortable table. This sort of elegance in design is never easy and the folks at Millstream Web Software have done a great job on this.

I used it on the Real Estate site I’m working on webdigs.com/mywebdigs (free sign up required) to organize our users favorite saved houses. I’ve found myself using it a lot for my own home search.

Happy Birthday Symfony (1st Symfony recruiter call, Zend Framework, & a happy client)

This last week has been really got me feeling pretty positive about the future of the Symfony PHP framework.

Last Thursday Erica Guay (erica D0T guay AT Sapphire.com) called me and is the first recruiter to ask me if I knew the Symfony framework. She’s got a pretty awesome opportunity near Boston, MA to fill if anyone is interested.

The next Tuesday I went to a presentation at Sierra Bravo here in Minneapolis, MN about the Zend Framework and Lucene. Maybe I’m a fanboy but it really looks like Zend has some catching up to do. Lucene is impressive and there is a Symfony plugin for it. Justin, Tom, and the rest of the Sierra Bravo Crew have already cranked out 5 Zend Framework sites but when I showed them the development environment in Symfony someone in the crowd literally said “wow”. It was admittedly a pretty nerdy bunch, when Justin (the presenter) mentioned a design patterns book he liked the guy next to me tapped his chest and made the peace line and said “that’s who I’m down with” If you are interested in attending their next presentation RSVP here.

The 3rd thing that reminded me how great Symfony is was just today when I showed a client an Admin Crud that I had created in about 30 min. He was pretty excited, and it’s nice to have happy clients.

Yahoo Pipes is kind of a big deal

Yahoo Pipes allows anyone to create their own mashup in a method very similar to creating a flowchart.

After messing around just a little I was able to create 2 pretty cool little mashups:

The first one geocodes garage sale locations and puts them on a map.  Not very well, but it was really easy to do, so it was still worth the effort

The second one combines 5 different Job search feeds into one, checks to see if the titles are unique, and then sorts the results in descending order by published date. I still prefer indeed.com for looking for Job information, but if I can also grab the feed to format myself to fix those problems.

All in all, it’s pretty cool stuff.  I think I have a real world use for an aggregated real estate news feed on www.webdigs.com already

Symfony PHP Framework – My first 10 months

This is a narrative of my experience with the Symfony PHP 5 framework over the last 10 months. The short story is that I’m a fan, but it’s not perfect.

Comming Soon: “PHP Web 2.0 Mashup Projects” Book Review

I should be receiving a “PHP Web 2.0 Mashup Projects” from PACKT publishing in the mail sometime soon, once I get it I’ll try to have a review up in a couple of weeks.

If you are interested check out the full book description at PACKT publishing

I’m pretty excited about it, I was planning on starting a personal mashup project to link Craigslist garage sale postings with google maps, so this book is coming along at exactly the right time.

Usability and creativity

So I was taking my morning stroll through popUrls when I came across two articles. One about CrazyEgg, a really cool tool for gathering usability statistics, and the other this really random, unconventional, whimsical, unprofessional, unattractive, outdated, not entirely original, and really really effective website for some hippie named Miranda July. (FYI – I call anyone who puts effort into being creative a hippie, although if they are mall style crafty I just block them out of my mind)

Anyway Miranda July’s site is a mess, she did it with a camera, a fridge, & a stove. However, despite me being 100% UNinterested in her product I read ALL THE AD COPY. I haven’t even read all the add copy for sites I’ve made myself, it’s miserable marketing gobly-gook because that’s what clients expect from “professionals”.

I think this sort of grass-roots, direct from the source w/0 any middle men, advertising is what will cut through the ad clutter so well described in this Frontline episode.

The lesson here is that once again “Content is King” and the ROI on details is difficult to gauge and probably not a high as most people think.

However…. if we go back to the the majority of the the marketing messages I(we) need to communicate a camera and a kitchen full of appliances might not be a sufficient tool-set, and a client probably wouldn’t “get it” anyway.

Enter CrazyEgg I read a really good introduction at Read/Write Web. I haven’t tried it yet but I think I’m going to push for it on my current project.

I’m tired of writing

robert

Jay Pipes MySQL bottlenecks lecture

So last Thursday the majority of the MoCo Web Development team and I attended the MySQL Meetup featuring Jay Pipes of MySQL. It was hosted at Metro State U. in Minneapolis, MN the facility was top notch (read: they had pie and coffee

Jay Pipes is an excellent speaker whose enthusiasm about MySQL is contagious. If you get a chance to hear him speak or back him into a corner for 20 questions I highly suggest it.

The thing Jay Spoke about that was most relevent to what I’m doing now was Horizontal Partitioning [article] [manual] which is taking a table with a very large amount of rows and breaking that heap into a series of smaller groups that can be intelligently loaded into memory on a as needed basis.

The obvious way for to do this for my, and I’d assume many projects is by, year. This would speed things up because my project’s users are most likely to only need to access the current year’s information. If I only ask MySQL to load up a table containing the current year then that’s a much smaller bite to chew.

Horizontal Partitioning can also be done with a hash of one or more other columns, which makes it very flexible, and very useful for medium to huge projects.

On a related topic I also realized that I’ve been asked by my supervisor to investigate the benefits of Vertical Partitioning before either of us knew what to call it. Essentially it’s breaking up tables and adding one-to-one relationships between them. This allows frequently accessed sections of the original table to be accessed in isolation without having to load a bunch of unused fields to memory. I’m lead to believe that some db’s handle this pragmatically but MySQL does not.

Another key points were that the most cost effective way to mitigate a MySQL bottleneck is to add more RAM to the server. This is essentially throwing money at the problem, but less money that the equivalent labor costs associated with other solutions.

Lastly the common yet important advice of “use the smallest data container possible” because “a BigINT is twice the size of a INT but a INT is twice as fast”.

There were many many other gems from this lecture, but I forgot my notes at work and I’m tired of writing

Update (4/1/06 9:21PM) — I found this useful list of tips & tricks also partially written by Jay Pipes http://forge.mysql.com/wiki/Top10SQLPerformanceTips 

Whoo hoo new blog….

watch this space become outdated very soon