Before:
mysql> explain SELECT galleryid, COUNT(*) as counter , `options`.`option_value` AS 'custom_post_id', GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROM `ngg_pictures` LEFT OUTER JOIN `options` ON `options`.option_name = CONCAT('ngg_pictures_', ngg_pictures.pid) LEFT OUTER JOIN `postmeta` ON `postmeta`.`post_id` = `options`.`option_value` WHERE (`galleryid` IN (1746, 1747, 1749, 1748, 1750, 1751, 1752, 1753, 1745, 1754)) GROUP BY galleryid, ngg_pictures.pid;
+----+-------------+--------------+------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | ngg_pictures | ALL | NULL | NULL | NULL | NULL | 51246 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | options | ALL | option_name | NULL | NULL | NULL | 4269 | |
| 1 | SIMPLE | postmeta | ref | post_id | post_id | 8 | _wp04.options.option_value | 5 | |
+----+-------------+--------------+------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
3 rows in set (0.19 sec)
After creating an index on [ngg_pictures,pid] (the latter may be unnecessary)
mysql> explain SELECT galleryid, COUNT(*) as counter , `options`.`option_value` AS 'custom_post_id', GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROM `ngg_pictures` LEFT OUTER JOIN `options` ON `options`.option_name = CONCAT('ngg_pictures_', ngg_pictures.pid) LEFT OUTER JOIN `postmeta` ON `postmeta`.`post_id` = `options`.`option_value` WHERE (`galleryid` IN (1746, 1747, 1749, 1748, 1750, 1751, 1752, 1753, 1745, 1754)) GROUP BY galleryid, ngg_pictures.pid;
+----+-------------+--------------+-------+---------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | ngg_pictures | range | galleryid | galleryid | 8 | NULL | 423 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | options | ALL | option_name | NULL | NULL | NULL | 4324 | |
| 1 | SIMPLE | postmeta | ref | post_id | post_id | 8 | _wp04.options.option_value | 5 | |
+----+-------------+--------------+-------+---------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
3 rows in set (7.65 sec)