Multiple LEFT JOIN clauses Very Slow (1 post)

  1. Master_Kato
    Posted 9 years ago #

    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.


Topic Closed

This topic has been closed to new replies.

About this Topic