Hi, I wrote a plugin to sort by up to 4 Custom Fields. I'll make it avilable but right now it's extremely slow and I'm looking for ways to optimize it.
I use the posts_orderby and posts_join filters. The posts_orderby part of the plugin is straightforward:
$orderby = $sortfield1.','.$sortfield2...etc Also has an option for ASC and DESC
The JOIN part goes like this:
LEFT JOIN wp_postsmeta AS $sortfield1 ON ID = post_id AND $sortfield1.meta_key = $sortfield1
This is actually in a loop to create up to 4 JOIN clauses depending on how many custom fields you want to sort by.
The problem is that 1 JOIN is okay, but upon adding the second JOIN my database query goes from < 1 second to 30 seconds or more. Additional JOIN clauses beyond two add a few more seconds.
I'm not sure how else to form the query. I was thinking about upgrading MySQL server, I've got 3.23, but wasn't sure if it would make much of a difference.