While shutting down terminal windows to the SN servers this morning, one of them had the create table syntax for the comments table still on screen. I gave it a read through just because it was there and noticed that there wasn't an index for the opid (top level comment id for speeding up entire thread pulling) column. So I got some before numbers, added one, and ran some After tests. Heavily commented stories show a 50-150% pages-per-second speed increase in threaded views. Low hanging fruit FTW.
(Score: 2, Interesting) by Anonymous Coward on Wednesday January 09 2019, @07:52PM (1 child)
With that discovery, I wonder if there are other missing indexes or with columns in the wrong order. Maybe you should try some EXPLAIN QUERY PLAN queries with your usual DML statements. That might also reveal instances where a covering index might be handy. An ANALYZE statement may also help your query planner (depending on your DB engine), but that may not be worth the minimal gain.
(Score: 1, Interesting) by Anonymous Coward on Thursday January 10 2019, @03:12AM
Just an FYI: The statement EXPLAIN QUERY PLAN isn't standard SQL, so the exact command differs depending on the exact implementation you are using. Sometimes it is EXPLAIN QUERY PLAN, EXPLAIN ANALYZE, or EXPLAIN VERBOSE, among some of the other options.