Slow query that loads the comments in the Admin UI (was: [VOTE] Release Roller 5.1.2)

[Kohei Nozaki <ky...@bridge9.sakura.ne.jp>]

Hi Matt,

I'm trying to solve your slow query issue in the comment management page 
which filed as https://issues.apache.org/jira/browse/ROL-2092.

Could you try creating the following index if you don't have one yet?

create index co_posttime_idx on roller_comment( posttime );

I can see the message "Sort aborted" in the error page so I suspect that 
sorting without indexes is the cause, and indexes would make sorting 
efficient.

Kohei

On 3/3/15 04:29, Matt Raible 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/0B9kkDCT2WDMXNE9XZl9LSXVHbmVjeklyVnBGNm9jd3p4R0gw/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 <ky...@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 <sn...@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
>>
>>
>