I am noticing that the following query takes a long time. The server at times stops responding after this. I am not able to understand Rows_examined: 150567106. Why is that such a high number? This may be an issue with my server. But the issue is only with this query. # Query_time: 3451.320230 Lock_time: 0.000093 Rows_sent: 0 Rows_examined: 150567106 SET timestamp=1393827869; SELECT P.guid, P.uniquePluginId, P.displayAs, P.category, P.webDirectoryName, P.versionString, P.shortDescription, (SELECT Count(id) AS count FROM bt_items AS I WHERE I.uniquePluginId = P.uniquePluginId AND I.appGuid = 'EADD49C9C08E0202E479A80F9' ) AS countOfScreens FROM bt_plugins AS P LEFT JOIN bt_items AS I ON P.uniquePluginId = I.uniquePluginId GROUP BY P.uniquePluginId ORDER BY countOfScreens DESC;

Do you have myPhpAdmin access? If so, take a look at your database and check out the bt_plugins table - how many rows does it show in that table? --------------------------------- https://buzz-tools.com

Yup. BT_items also. This query is counting the number of items per "plugin type." I'll be this is the query that runs when you're "viewing screens" in the app. BT_screen.php I think. Depending on the backend, there are a zilion ways to tune your database. If you're running a large, busy, high volume server it gets even more complex. The biggest challenges come when you combine a huge database (tens of thousands or rows), with a weak server (not much RAM) and a poorly tuned query. There are few queries in the self hosted package that are tuned. This is intenionall. Because of all the various ways to tune a query, and depending on the actual implementation, it's tought to get anything right without analyzing the actual implimentation. You can probably tune this query yourself without too much trouble. If you're daring? Try running an "EXPLAIN" (google it) to see how the mySQL engine is processing any indexes. I'll be no indexes are used and you'll see a "using filesort" or whatever. Cant' remember the words without looking at the docs. You'll need to index some columns (ALTER TABLE CREATE INDEX, again, google it) in order to tune it up. I'll be adding a few simple indexes, without much detail, like say on the P.uniquePluginId column, or just removing the ORDER BY statement will speed this up a ton. ORDER BY's are notoriously difficult to tune. Hope this helps.

Thanks Chris & David will try the suggestions and get back to you. I have 1 GB of RAM and using Linode. So have full access to the server. Bt_plugins has 44 rows. BT_items has 45762 rows. Hoping that creating index will resolve this also I can live by without ORDER BY.

Updated screens.php, much much better now: https://www.buzztouch.com/forum/thread.php?tid=442DA0DDF0A2A3194110922