Modifying the query to remove the join, I guess would be the way to go for a single user install.
...Personally I always tend to add immutable foreign keys to files as I do not like doing queries for users. But then I prefer composite keys (no joins with id's) which I guess is another matter all together! :) Cheers Greg On 4 March 2015 at 14:06, Glen Mazza <glen.ma...@gmail.com> wrote: > We presently have indexes on the entryid in roller_comment (line 236) and > on website_id in weblogentry (line 174): http://svn.apache.org/viewvc/ > roller/trunk/app/src/main/resources/sql/createdb.vm? > revision=1625869&view=markup. It wouldn't be a disaster, but I'm inclined > against denormalization (duplicating website ids in the roller_comment > table) as I believe a relational model is a strong selling point for > Roller, it's better to let heavy users do what tweaking--whatever indexes > or database changes--they need to do to support their custom needs. > > Since Matt's blog is the only blog on his instance, he can also do some of > the same comment management on the global comment management screen, which > doesn't have that join. Actually, it would be good for him to do that just > to see if the problem goes away there (i.e., it's a database join issue), > or if it doesn't then it might be a memory problem unrelated to the join. > > The indexes could also possibly be hurting him because there's only one > website id for his blog server and it's inefficient to go to indexes > instead of the tables directly when every record they would find would be > fitting anyway. > > Matt can also remove the join from the .orm.xml file, do an mvn clean > install to get a custom build and then deploy that. His blog comment > management page would list all comments, but that wouldn't matter as he > just has one blog. I think Matt had earlier stated he has a non-standard > Roller installation that doesn't lend itself to an automatic upgrade so > some hacking on his part is probably going to be needed anyway if he wishes > to upgrade. > > Glen > > On 03/03/2015 03:29 AM, Greg Huber wrote: > >> SELECT c FROM WeblogEntryComment c WHERE c.weblogEntry.website = ?1 ORDER >> BY c.postTime DESC >> >> Could experiment with some indexes..... or add websiteid to the >> roller_comment so it does not have to do the join with weblogEntry to get >> the website parameter #1. The websiteid does not change. >> >> On 2 March 2015 at 19:29, Matt Raible <m...@raibledesigns.com> wrote: >> >> Is it possible to optimize the query that loads the comments in the Admin >>> UI? When I click on the "comments" section (in the Admin UI), it takes a >>> really long time to load and I sometimes see the following error. The >>> sysadmin at my ISP (kgbinternet.com) said this SQL takes a long time to >>> run >>> and is sometimes killed by the server. >>> >>> >>> https://drive.google.com/file/d/0B9kkDCT2WDMXNE9XZl9LSXVHbmVje >>> klyVnBGNm9jd3p4R0gw/view?usp=sharing >>> >>> My blog has 3193 entries and 13,799 comments (since 2002). >>> >>> Thanks, >>> >>> Matt >>> >>> On Sun, Mar 1, 2015 at 6:04 PM, Kohei Nozaki <k...@bridge9.sakura.ne.jp> >>> wrote: >>> >>> Sorry I just found a problem to my patch which accepted in >>>> https://issues.apache.org/jira/browse/ROL-2063 . could you take a look >>>> >>> at >>> >>>> my new patch ROL-2063_update1.patch and comment in that JIRA? >>>> >>>> ROL-2063_update1.patch >>>> >>>> >>>> On Mar 2, 2015, at 4:56, Dave <snoopd...@gmail.com> wrote: >>>> >>>> Hi Roller fans, >>>>> >>>>> I would like to propose that we release Roller 5.1.2 based on the code >>>>> >>>> at >>> >>>> Subversion tag roller_5.1.2-rc1. You can download the source release, >>>>> convenience binaries and signatures for the release here: >>>>> >>>>> https://dist.apache.org/repos/dist/dev/roller/roller-5.1/v5.1.2/ >>>>> >>>>> The release contains a variety of mostly minor fixes which you can find >>>>> listed here: >>>>> >>>>> >>>>> https://issues.apache.org/jira/issues/?jql=project%20% >>> 3D%20ROL%20AND%20fixVersion%20%3D%205.1.2%20AND%20updated% >>> 20%3E%3D%20-20w%20ORDER%20BY%20updated%20DESC >>> >>>> Please vote within the next 72 hours. >>>>> >>>>> I'm voting +1 >>>>> >>>>> Thanks, >>>>> Dave >>>>> >>>> >>>> >