Slow query – Nextgen

I made a dirty change in the mixin.netgen_table_extras.php …

Line 153, function get_generated_query ()

Added some limitations:
$this->object->limit(20,0);
$this->object->order_by($primary_key, “DESC”);

We don’t use NextGen function for post_id’s (only use [ngggallery id=]), so I changed also the query:
$sql = str_replace(‘FROM’, “, {$wpdb->options}.option_value AS ‘custom_post_id’, GROUP_CONCAT(CONCAT_WS(‘@@’, meta_key, meta_value)) AS ‘extras’ FROM”, $sql);

in:
$sql = str_replace(‘FROM’, “, ” AS ‘custom_post_id’, ” AS ‘extras’ FROM”, $sql);

And also:
$sql = str_replace($from, “{$from} LEFT OUTER JOIN {$wpdb->options} ON {$wpdb->options}.option_name = CONCAT(‘{$table_name}_’, {$primary_key}) LEFT OUTER JOIN {$wpdb->postmeta} ON {$wpdb->postmeta}.post_id = {$wpdb->options}.option_value “, $sql);

$sql = str_replace($from, “{$from} LEFT OUTER JOIN {$wpdb->options} ON {$wpdb->options}.option_name = CONCAT(‘{$table_name}_’, {$primary_key})”, $sql);

I only show the last 20 galleries when adding pictues to a gallery.
Therefore also showing only the last 20 galleries in selectbox by Posts.

@nielsv_e – Thanks for your suggestion … we have also improved this part our our queries with our next (beta) release and expect it will have an over-all improvement with speed.

– Cais.

I have also started to see MySQL slow query log entries lately; I can’t say if it started with NextGEN Gallery version 2.0.40. The site has about 800 images in 40 galleries. Here’s a sample:

# Query_time: 14 Lock_time: 0 Rows_sent: 173 Rows_examined: 4431348
use s******;
SELECT galleryid, COUNT(*) as counter , wp_options.option_value AS ‘custom_post_id’, GROUP_CONCAT(CONCAT_WS(‘@@’, meta_key, meta_value)) AS ‘extras’ FROM wp_ngg_pictures LEFT OUTER JOIN wp_options ON wp_options.option_name = CONCAT(‘wp_ngg_pictures_’, wp_ngg_pictures.pid) LEFT OUTER JOIN wp_postmeta ON wp_postmeta.post_id = wp_options.option_value WHERE (galleryid IN (35, 38, 15, 16, 41, 17, 14, 43)) GROUP BY galleryid, wp_ngg_pictures.pid;