2.0.40 is poorly indexed

A friend’s blog went down this morning, the database was busy creating temp tables for Next Gen Gallery tables and had the options table locked up so nothing was working. Increased tmp_table_size to get around this and told him to update the plugin. Coincidentally he had updated it last night, so this sounds like a performance regression.

Here is the explain on the query that was gumming everything up:
mysql> explain SELECT galleryid, COUNT(*) as counter ,options.option_valueAS 'custom_post_id', GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROMngg_picturesLEFT OUTER JOINoptionsONoptions.option_name = CONCAT('ngg_pictures_', ngg_pictures.pid) LEFT OUTER JOINpostmetaONpostmeta.post_id=options.option_valueWHERE (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)`

I created an index on [galleryid, pid] which improved it to:

mysql> explain SELECT galleryid, COUNT(*) as counter ,options.option_valueAS 'custom_post_id', GROUP_CONCAT(CONCAT_WS('@@', meta_key, meta_value)) AS 'extras' FROMngg_picturesLEFT OUTER JOINoptionsONoptions.option_name = CONCAT('ngg_pictures_', ngg_pictures.pid) LEFT OUTER JOINpostmetaONpostmeta.post_id=options.option_valueWHERE (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)`

I’m sure there’s more that could be done, I just wanted to point this out because it’s going to cause a lot of grief for people with larger galleries.

Also, joining on options.value seems like a bad smell. That’s a key-value lookup table so the join is always going to force a table scan. Indexing it would be a bad idea. It’s just not meant to be looked up by value.

https://www.ads-software.com/plugins/nextgen-gallery/