Multiple LEFT JOIN clauses Very Slow

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.

matt