How many joins do you do in your case?

Like your example code, I use as many joins as I have distinct criteria. We typically only have a couple of sets of criteria, but sometimes it's five or six, and the performance on MySQL has been fine.

Here is the code i came up with so far.

I'd like to reiterate the importance of using placeholders, which allow for efficient query plan caching.

You could easily modify your sample code to return a parameterized query based simply on how many criteria pairs you had. You can then pass your pairs of meta-data criteria to the DBI as execute() parameters. It eliminates any worries about quoting and escaping the values, and may provide a performance benefit by simplifying the database's query parse/plan efforts.

sub GetNodes {
  my $meta = shift;
  my $sql = buildMetaQuery( scalar keys %$meta );
  my $sth = $dbh->prepare_cached($sql);
  $sth->execute( %$meta );
  $sth->fetchrow_array  
}
[download]

This assumes that buildMetaQuery( 3 ) returns something like:

SELECT md1.node_id
 FROM `meta_data` as md1,`meta_data` as md2,`meta_data` as md3
 WHERE md1.name = ?
   AND md1.value = ?
   AND md2.name = ?
   AND md2.value = ?
   AND md3.name = ?
   AND md3.value = ?
   AND md1.node_id = md2.node_id = md3.node_id;
[download]