gistfile1.php

<?php
for ($i=1; $i <= 10; $i++) {

$start = microtime(true);


for ($ii=0; $ii < 5000; $ii++) {

  $tids1 = $tids2 = $tids3 = array();
  for ($iii=0; $iii < 3; $iii++) {
    $tids1[] = rand(500, 1000000);
    $tids2[] = rand(500, 1000000);
    $tids3[] = rand(500, 1000000);
  }

  //Stuff the randomly selected tids into an array compatible with db_query
  $args = array(
    ":tids1" => $tids1,
    ":tids2" => $tids2,
    ":tids3" => $tids3,
  );

  //Get a ranked list of the 5 nodes which have the most matches
  $query = db_query(
    "SELECT nodeid, SUM(tagcount)
     FROM (
       SELECT node.nid nodeid, field_data_field_tags.field_tags_tid tag, COUNT(node.nid) tagcount
       FROM node
       LEFT JOIN field_data_field_tags
       ON node.nid=field_data_field_tags.entity_id
       WHERE field_data_field_tags.field_tags_tid
       IN (:tids1)
       GROUP BY nid

       UNION ALL SELECT node.nid nodeid, field_data_field_test.field_test_tid tag, COUNT(node.nid) tagcount
       FROM node
       LEFT JOIN field_data_field_test
       ON node.nid=field_data_field_test.entity_id
       WHERE field_data_field_test.field_test_tid
       IN (:tids2)
       GROUP BY nid

       UNION ALL SELECT node.nid nodeid, field_data_field_test2.field_test2_tid tag, COUNT(node.nid) tagcount
       FROM node
       LEFT JOIN field_data_field_test2
       ON node.nid=field_data_field_test2.entity_id
       WHERE field_data_field_test2.field_test2_tid
       IN (:tids3)
       GROUP BY nid
     ) subquery

     GROUP BY nodeid
     ORDER BY sum(tagcount) DESC
     LIMIT 5",
     $args
  );

  $result = $query->fetchAll();
}

print "Run $i: " . round(microtime(true) - $start, 3) . ' seconds' . "\n";
}