Bug report: performance low on large networks

Hi, I really love it and I need a way to “slow it down”. My Network has quite a few sites and scanning for new posts seems to be a performance issue than. Everything is fine in dev environment but in production I hat to stop the plugin as it causes timeouts on the database.

To Reproduce: Set up a network of 500+ Sites with content. Start the Plugin. Query your database stats: SELECT id,time_ms,info FROM information_schema.PROCESSLIST;

I regularly see up to 150 queries like the following samples _in_parallel_ with long running times.

Seems to me like the new posts queries spawn before previous queries finished. this causes alot of IO.

information_schema.PROCESSLIST;
| id    | time_ms   | info                                                                                                                                                                                                                                                            |
+-------+-----------+-----------------+
| 24847 |   168.399 | INSERT INTO wp_network_posts_results SELECT ID,951 as blog_id,post_title,post_excerpt,post_content,post_author,post_date,post_type FROM wp_951_posts  WHERE (post_type='post') AND (wp_951_posts.post_password='') A
ND (post_status="publish") 
+-------+-----------+-----------------+
| 24824 |  9063.069 | CREATE TEMPORARY TABLE wp_network_posts_results AS SELECT ID,1023 as blog_id,post_title,post_excerpt,post_content,post_author,post_date,post_type FROM wp_1023_posts  WHERE (post_type='post') AND (wp_1023_posts.post_password='') AND (post_status="publish") |
+-------+-----------+-----------------+
| 24802 | 11192.990 | CREATE TEMPORARY TABLE wp_network_posts_results AS SELECT ID,1023 as blog_id,post_title,post_excerpt,post_content,post_author,post_date,post_type FROM wp_1023_posts  WHERE (post_type='post') AND (wp_1023_posts.post_password='') AND (post_status="publish") |
+-------+-----------+-----------------+
| 24698 | 12670.369 | CREATE TEMPORARY TABLE wp_network_posts_results AS SELECT ID,1023 as blog_id,post_title,post_excerpt,post_content,post_author,post_date,post_type FROM wp_1023_posts  WHERE (post_type='post') AND (wp_1023_posts.post_password='') AND (post_status="publish") |

Apparently the plugin also starts multiple parallel sessions (otherwise multiple tables with the same name would not be possible) to the database. This could also further drain performance on a webserver itself.

From my understanding and brief investigation I suggest some kind of “query is running” flag somewhere to prevent triggering new queries before previous ones are finished. Another, on large scale even better option would be to deactivate whatever triggers it at all and have scanning handed to e.g. a cron task.

Thanks again for making this plugin, I hope my input is understood as constructive as it was ment. I would love to keep using the plugin even in our larger.

hinnerk