[bbDev] [1557] trunk/bb-includes/functions.php: _bb_append_meta_sort() - sorts meta keys by length to ensure $appended_object->{$bbdb-> prefix}key overwrites $appended_object->key as desired for trunk

[Michael D Adams]

I tried a number of methods on a live server with about half a million  
user rows and three million usermeta rows to see which was fastest.

The first one (the one I committed) was the fastest.  Both PHP sorting  
methods were faster than any of the MySQL order methods.

// No SQL ORDER BY - sort in PHP by key length
function php_len_sort() {
         global $bbdb;
         $rand = mt_rand();

         $sql = "SELECT meta_key, meta_value FROM bb_usermeta WHERE  
user_id = 1 AND $rand";

         $results = $bbdb->get_results( $sql );
         usort( $results, '_php_len_sort' );
}

function _php_len_sort( $a, $b ) {
         return strlen($a->meta_key) - strlen($b->meta_key);
}

// ORDER BY LENGTH
function mysql_len_sort() {
         global $bbdb;
         $rand = mt_rand();

         $sql = "SELECT meta_key, meta_value FROM bb_usermeta WHERE  
user_id = 1 AND $rand ORDER BY LENGTH( meta_key )";

         $results = $bbdb->get_results( $sql );
}

// No SQL ORDER BY - sort in PHP by whether or not string starts with  
prefix
function php_beg_sort() {
         global $bbdb;
         $rand = mt_rand();

         $sql = "SELECT meta_key, meta_value FROM bb_usermeta WHERE  
user_id = 1 AND $rand";

         $results = $bbdb->get_results( $sql );
         usort( $results, '_php_beg_sort' );
}

function _php_beg_sort( $a, $b ) {
         $_a = strpos( $a->key, 'bb_' );
         if ( $_a === strpos($b->key, 'bb_') )
                 return 0;
         return 0 === $a ? -1 : 1;
}

// ORDER BY LIKE match
function mysql_beg_like_sort() {
         global $bbdb;
         $rand = mt_rand();

         $sql = "SELECT meta_key, meta_value FROM bb_usermeta WHERE  
user_id = 1 AND $rand ORDER BY meta_key LIKE 'bb_%'";

         $results = $bbdb->get_results( $sql );
}

// ORDER BY INSTR
function mysql_beg_instr_sort() {
         global $bbdb;
         $rand = mt_rand();

         $sql = "SELECT meta_key, meta_value FROM bb_usermeta WHERE  
user_id = 1 AND $rand ORDER BY 1 = INSTR( meta_key, 'bb_' )";

         $results = $bbdb->get_results( $sql );
}