optimise SQL wordpress call

Question

I have this code :

$querystr = "
SELECT *
FROM {$wpdb->prefix}posts
INNER JOIN {$wpdb->prefix}postmeta m1
  ON ( {$wpdb->prefix}posts.ID = m1.post_id )
INNER JOIN {$wpdb->prefix}postmeta m2
  ON ( {$wpdb->prefix}posts.ID = m2.post_id )
WHERE
{$wpdb->prefix}posts.post_type = 'produits'
OR {$wpdb->prefix}posts.post_type = 'coupsdecoeur'
AND {$wpdb->prefix}posts.post_status = 'publish'
GROUP BY {$wpdb->prefix}posts.ID
ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";

when loading page containing this so slooooooowwwww, is there a trick to optimize this ?

I just need the post title and a meta value called "wpcf-lien".

Thanks a lot.

Answer 1

The query itself may need to be optimized (SQL is not my speciality, though). However, don't forget you can store the results of an expensive operation in cached memory. WordPress offers a Transients API to make things easy. Here’s a quick example:

// Try to load the value from cache
$transient_key = 'my_query';
$value = get_transient( $transient_key );

// If no cached value was found, perform the query and cache it
if ( false === $value ) {
    $value = $wpdb->get_results( 'SELECT * FROM ...' );
    set_transient( $transient_key, $value, HOUR_IN_SECONDS );
}

Answer 2

How do you know this is the bottleneck in your page? If the problem really is on this query, you can try adding an index on post_type and post_status.

There are plenty of cache plugins that can also handle this for you.

Answer 3

My advice is to stop trying to do direct sql, and use a normal WP_Query instead. Then it does lots of caching for you. And caching plugins can help with the default systems.