How to find Confluence SQL queries that need this lower computed column and index

- - 1. confluence administration - Enable SQL statement profiling
  - 2. tail -f atlassian-confluence.log | grep -i lower
    - Note: Might be able to make an even better one that sorts the output, and counts lines

                     2010-09-15 10:10:44,777 DEBUG [http-8080-4] [net.sf.hibernate.SQL] log select attachment0_.ATTACHMENTID as ATTACHME1_, attachment0_.TITLE as TITLE, attachment0_.CONTENTTYPE as CONTENTT3_, attachment0_.PAGEID as PAGEID, attachment0_.CREATOR as CREATOR, attachment0_.CREATIONDATE as CREATION6_, attachment0_.LASTMODIFIER as LASTMODI7_, attachment0_.LASTMODDATE as LASTMODD8_, attachment0_.FILESIZE as FILESIZE, attachment0_.ATTACHMENT_COMMENT as ATTACHM10_, attachment0_.ATTVERSION as ATTVERSION, attachment0_.PREVVER as PREVVER from ATTACHMENTS attachment0_ where (attachment0_.PAGEID=? )and(lower(attachment0_.TITLE)=? )and(attachment0_.PREVVER is null )
-- Saw this 34 times
-- Used in building dashboard

This one is different, see the WHERE clause:
                     2010-09-15 10:14:46,071 DEBUG [http-8080-2] [net.sf.hibernate.SQL] log select attachment0_.ATTACHMENTID as ATTACHME1_, attachment0_.TITLE as TITLE, attachment0_.CONTENTTYPE as CONTENTT3_, attachment0_.PAGEID as PAGEID, attachment0_.CREATOR as CREATOR, attachment0_.CREATIONDATE as CREATION6_, attachment0_.LASTMODIFIER as LASTMODI7_, attachment0_.LASTMODDATE as LASTMODD8_, attachment0_.FILESIZE as FILESIZE, attachment0_.ATTACHMENT_COMMENT as ATTACHM10_, attachment0_.ATTVERSION as ATTVERSION, attachment0_.PREVVER as PREVVER from ATTACHMENTS attachment0_ where (attachment0_.PAGEID=? )and(lower(attachment0_.TITLE)=? )and(attachment0_.ATTVERSION=? )
-- Saw this 5 times

 2010-09-15 10:12:15,455 DEBUG [DefaultQuartzScheduler_Worker-8] [net.sf.hibernate.SQL] log select top 1 trackbackl0_.LINKID as LINKID, trackbackl0_.VIEWCOUNT as VIEWCOUNT, trackbackl0_.URL as URL, trackbackl0_.TITLE as TITLE, trackbackl0_.BLOGNAME as BLOGNAME, trackbackl0_.EXCERPT as EXCERPT, trackbackl0_.CONTENTID as CONTENTID, trackbackl0_.CREATOR as CREATOR, trackbackl0_.CREATIONDATE as CREATIO10_, trackbackl0_.LASTMODIFIER as LASTMOD11_, trackbackl0_.LASTMODDATE as LASTMOD12_ from TRACKBACKLINKS trackbackl0_ where (trackbackl0_.CONTENTID=? )and(lower(trackbackl0_.URL)=? )
-- Saw this 8 times

 2010-09-15 10:12:15,476 DEBUG [DefaultQuartzScheduler_Worker-8] [net.sf.hibernate.SQL] log select top 1 referralli0_.LINKID as LINKID, referralli0_.VIEWCOUNT as VIEWCOUNT, referralli0_.URL as URL, referralli0_.CONTENTID as CONTENTID, referralli0_.CREATOR as CREATOR, referralli0_.CREATIONDATE as CREATION7_, referralli0_.LASTMODIFIER as LASTMODI8_, referralli0_.LASTMODDATE as LASTMODD9_ from EXTRNLNKS referralli0_ where (referralli0_.CONTENTID=? )and(lower(referralli0_.URL)=? )
-- Saw this 8 times

                    2010-09-15 10:12:21,746 DEBUG [http-8080-26] [net.sf.hibernate.SQL] log select page0_.CONTENTID as CONTENTID0_, comments1_.CONTENTID as CONTENTID1_, children2_.CONTENTID as CONTENTID2_, page0_.CHILD_POSITION as CHILD_P13_0_, page0_.PARENTID as PARENTID0_, page0_.SPACEID as SPACEID0_, page0_.TITLE as TITLE0_, page0_.VERSION as VERSION0_, page0_.CREATOR as CREATOR0_, page0_.CREATIONDATE as CREATION6_0_, page0_.LASTMODIFIER as LASTMODI7_0_, page0_.LASTMODDATE as LASTMODD8_0_, page0_.VERSIONCOMMENT as VERSIONC9_0_, page0_.PREVVER as PREVVER0_, page0_.CONTENT_STATUS as CONTENT11_0_, comments1_.PAGEID as PAGEID1_, comments1_.PARENTCOMMENTID as PARENTC21_1_, comments1_.TITLE as TITLE1_, comments1_.VERSION as VERSION1_, comments1_.CREATOR as CREATOR1_, comments1_.CREATIONDATE as CREATION6_1_, comments1_.LASTMODIFIER as LASTMODI7_1_, comments1_.LASTMODDATE as LASTMODD8_1_, comments1_.VERSIONCOMMENT as VERSIONC9_1_, comments1_.PREVVER as PREVVER1_, comments1_.CONTENT_STATUS as CONTENT11_1_, children2_.PAGEID as PAGEID2_, children2_.PARENTCOMMENTID as PARENTC21_2_, children2_.TITLE as TITLE2_, children2_.VERSION as VERSION2_, children2_.CREATOR as CREATOR2_, children2_.CREATIONDATE as CREATION6_2_, children2_.LASTMODIFIER as LASTMODI7_2_, children2_.LASTMODDATE as LASTMODD8_2_, children2_.VERSIONCOMMENT as VERSIONC9_2_, children2_.PREVVER as PREVVER2_, children2_.CONTENT_STATUS as CONTENT11_2_, children2_.PARENTCOMMENTID as PARENTC21___, children2_.CONTENTID as CONTENTID__ from CONTENT page0_ left outer join CONTENT comments1_ on page0_.CONTENTID=comments1_.PAGEID left outer join CONTENT children2_ on comments1_.CONTENTID=children2_.PARENTCOMMENTID where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=? )and(lower(page0_.TITLE)=? )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' )) order by children2_.CREATIONDATE
-- Saw this 5 times

                    2010-09-15 10:12:21,829 DEBUG [http-8080-26] [net.sf.hibernate.SQL] log select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_, page0_.LASTMODIFIER as LASTMODI7_, page0_.LASTMODDATE as LASTMODD8_, page0_.VERSIONCOMMENT as VERSIONC9_, page0_.PREVVER as PREVVER, page0_.CONTENT_STATUS as CONTENT11_ from CONTENT page0_ where page0_.CONTENTTYPE='PAGE' and ((page0_.SPACEID=? )and(lower(page0_.TITLE)=? )and(page0_.PREVVER is null )and(page0_.CONTENT_STATUS='current' ))
-- Saw this 22 times
-- used in building dashboard

                     2010-09-15 10:15:24,670 DEBUG [http-8080-2] [net.sf.hibernate.SQL] log select top 20 labelling1_.ID as ID, labelling1_.LABELID as LABELID, labelling1_.CONTENTID as CONTENTID, labelling1_.OWNER as OWNER, labelling1_.CREATIONDATE as CREATION5_, labelling1_.LASTMODDATE as LASTMODD6_ from LABEL label0_, CONTENT_LABEL labelling1_, CONTENT contentent2_ where (lower(label0_.OWNER)=? )and(label0_.NAMESPACE='my' )and(labelling1_.LABELID=label0_.LABELID )and(contentent2_.CONTENT_STATUS!='deleted'  and labelling1_.CONTENTID=contentent2_.CONTENTID) order by  labelling1_.LASTMODDATE desc

                     2010-09-15 10:15:24,761 DEBUG [http-8080-2] [net.sf.hibernate.SQL] log select top 20 label3_.LABELID as LABELID, label3_.NAME as NAME, label3_.OWNER as OWNER, label3_.NAMESPACE as NAMESPACE, label3_.CREATIONDATE as CREATION5_, label3_.LASTMODDATE as LASTMODD6_, label3_.LABELID as x0_0_, count(label3_.LABELID) as x1_0_ from CONTENT_LABEL labelling0_, CONTENT spaceconte1_, SPACES space2_, LABEL label3_, CONTENT contentent4_ where spaceconte1_.CONTENTTYPE in ('com.atlassian.confluence.pages.AbstractPage', 'USERSTATUS', 'PAGE', 'BLOGPOST', 'MAIL', 'SPACEDESCRIPTION', 'com.atlassian.confluence.core.SpaceContentEntityObject') and labelling0_.LABELID=label3_.LABELID and ((label3_.NAMESPACE=?  and labelling0_.LABELID=label3_.LABELID)and(labelling0_.CONTENTID=spaceconte1_.CONTENTID )and(spaceconte1_.SPACEID=space2_.SPACEID )and(lower(space2_.SPACEKEY)=? )and(contentent4_.CONTENT_STATUS!='deleted'  and labelling0_.CONTENTID=contentent4_.CONTENTID)) group by  label3_.LABELID , label3_.NAMESPACE , label3_.OWNER , label3_.NAME , label3_.LASTMODDATE , label3_.CREATIONDATE order by  count(label3_.LABELID)DESC
                                                                                        log select top 100 label3_.LABELID as LABELID, label3_.NAME as NAME, label3_.OWNER as OWNER, label3_.NAMESPACE as NAMESPACE, label3_.CREATIONDATE as CREATION5_, label3_.LASTMODDATE as LASTMODD6_, label3_.LABELID as x0_0_, count(label3_.LABELID) as x1_0_ from CONTENT_LABEL labelling0_, CONTENT spaceconte1_, SPACES space2_, LABEL label3_, CONTENT contentent4_ where spaceconte1_.CONTENTTYPE in ('com.atlassian.confluence.pages.AbstractPage', 'USERSTATUS', 'PAGE', 'BLOGPOST', 'MAIL', 'SPACEDESCRIPTION', 'com.atlassian.confluence.core.SpaceContentEntityObject') and labelling0_.LABELID=label3_.LABELID and ((label3_.NAMESPACE=?  and labelling0_.LABELID=label3_.LABELID)and(labelling0_.CONTENTID=spaceconte1_.CONTENTID )and(spaceconte1_.SPACEID=space2_.SPACEID )and(lower(space2_.SPACEKEY)=? )and(contentent4_.CONTENT_STATUS!='deleted'  and labelling0_.CONTENTID=contentent4_.CONTENTID)) group by  label3_.LABELID , label3_.NAMESPACE , label3_.OWNER , label3_.NAME , label3_.LASTMODDATE , label3_.CREATIONDATE order by  count(label3_.LABELID)DESC
-- saw 16 times


                     2010-09-15 10:18:04,942 DEBUG [http-8080-8] [net.sf.hibernate.SQL] log select blogpost0_.CONTENTID as CONTENTID, blogpost0_.SPACEID as SPACEID, blogpost0_.TITLE as TITLE, blogpost0_.VERSION as VERSION, blogpost0_.CREATOR as CREATOR, blogpost0_.CREATIONDATE as CREATION6_, blogpost0_.LASTMODIFIER as LASTMODI7_, blogpost0_.LASTMODDATE as LASTMODD8_, blogpost0_.VERSIONCOMMENT as VERSIONC9_, blogpost0_.PREVVER as PREVVER, blogpost0_.CONTENT_STATUS as CONTENT11_ from CONTENT blogpost0_, SPACES space1_ where blogpost0_.CONTENTTYPE='BLOGPOST' and ((lower(space1_.SPACEKEY)=?  and blogpost0_.SPACEID=space1_.SPACEID)and(blogpost0_.CREATIONDATE>=? )and(blogpost0_.CREATIONDATE<? )and(blogpost0_.PREVVER is null )and(blogpost0_.CONTENT_STATUS='current' ))
-- Saw this 2 times

                     2010-09-15 10:18:04,998 DEBUG [http-8080-8] [net.sf.hibernate.SQL] log select top 1 blogpost0_.CONTENTID as CONTENTID, blogpost0_.SPACEID as SPACEID, blogpost0_.TITLE as TITLE, blogpost0_.VERSION as VERSION, blogpost0_.CREATOR as CREATOR, blogpost0_.CREATIONDATE as CREATION6_, blogpost0_.LASTMODIFIER as LASTMODI7_, blogpost0_.LASTMODDATE as LASTMODD8_, blogpost0_.VERSIONCOMMENT as VERSIONC9_, blogpost0_.PREVVER as PREVVER, blogpost0_.CONTENT_STATUS as CONTENT11_ from CONTENT blogpost0_, SPACES space1_ where blogpost0_.CONTENTTYPE='BLOGPOST' and ((lower(space1_.SPACEKEY)=?  and blogpost0_.SPACEID=space1_.SPACEID)and(blogpost0_.CREATIONDATE>? )and(blogpost0_.PREVVER is null )and(blogpost0_.CONTENT_STATUS='current' )) order by  blogpost0_.CREATIONDATE asc
                     2010-09-15 10:18:05,004 DEBUG [http-8080-8] [net.sf.hibernate.SQL] log select top 1 blogpost0_.CONTENTID as CONTENTID, blogpost0_.SPACEID as SPACEID, blogpost0_.TITLE as TITLE, blogpost0_.VERSION as VERSION, blogpost0_.CREATOR as CREATOR, blogpost0_.CREATIONDATE as CREATION6_, blogpost0_.LASTMODIFIER as LASTMODI7_, blogpost0_.LASTMODDATE as LASTMODD8_, blogpost0_.VERSIONCOMMENT as VERSIONC9_, blogpost0_.PREVVER as PREVVER, blogpost0_.CONTENT_STATUS as CONTENT11_ from CONTENT blogpost0_, SPACES space1_ where blogpost0_.CONTENTTYPE='BLOGPOST' and ((lower(space1_.SPACEKEY)=?  and blogpost0_.SPACEID=space1_.SPACEID)and(blogpost0_.CREATIONDATE<? )and(blogpost0_.PREVVER is null )and(blogpost0_.CONTENT_STATUS='current' )) order by  blogpost0_.CREATIONDATE desc
-- 2 different queries, see sort order


                    2010-09-15 10:22:04,483 DEBUG [http-8080-13] [net.sf.hibernate.SQL] log select label0_.LABELID as LABELID, label0_.NAME as NAME, label0_.OWNER as OWNER, label0_.NAMESPACE as NAMESPACE, label0_.CREATIONDATE as CREATION5_, label0_.LASTMODDATE as LASTMODD6_ from LABEL label0_ where (label0_.NAME=? )and(label0_.NAMESPACE=? )and(lower(label0_.OWNER)is null )
-- 1 query




                                                                                        log select space0_.SPACEID as SPACEID, space0_.SPACENAME as SPACENAME, space0_.SPACEKEY as SPACEKEY, space0_.SPACEDESCID as SPACEDES4_, space0_.HOMEPAGE as HOMEPAGE, space0_.CREATOR as CREATOR, space0_.CREATIONDATE as CREATION7_, space0_.LASTMODIFIER as LASTMODI8_, space0_.LASTMODDATE as LASTMODD9_, space0_.SPACETYPE as SPACETYPE, space0_.SPACEGROUPID as SPACEGR11_ from SPACES space0_ where (lower(space0_.SPACEKEY)=? )
 -- saw 2 times


2010-09-15 10:39:58,159 DEBUG [http-8080-2] [net.sf.hibernate.SQL]                      log select distinct space0_.SPACEID as SPACEID, space0_.SPACENAME as SPACENAME, space0_.SPACEKEY as SPACEKEY, space0_.SPACEDESCID as SPACEDES4_, space0_.HOMEPAGE as HOMEPAGE, space0_.CREATOR as CREATOR, space0_.CREATIONDATE as CREATION7_, space0_.LASTMODIFIER as LASTMODI8_, space0_.LASTMODDATE as LASTMODD9_, space0_.SPACETYPE as SPACETYPE, space0_.SPACEGROUPID as SPACEGR11_ from SPACES space0_, CONTENT_LABEL labelling1_, LABEL label2_ where (space0_.SPACEDESCID=labelling1_.CONTENTID )and(labelling1_.LABELID=label2_.LABELID )and((label2_.NAME='favourite' )or(label2_.NAME='favorite' ))and(lower(label2_.OWNER)=? )and(label2_.NAMESPACE='my' ) order by  space0_.SPACENAME
-- Used in building dashboard



2010-09-15 10:39:58,368 DEBUG [http-8080-2] [net.sf.hibernate.SQL]                      log select label0_.LABELID as LABELID, label0_.NAME as NAME, label0_.OWNER as OWNER, label0_.NAMESPACE as NAMESPACE, label0_.CREATIONDATE as CREATION5_, label0_.LASTMODDATE as LASTMODD6_ from LABEL label0_ where (label0_.NAME=? )and(label0_.NAMESPACE=? )and(lower(label0_.OWNER)=? )
-- Used in building dashboard
-- saw 2 times

Implement LOWER computed fields and indexes for other tables that need this

More info on this problem with content table - http://confluence.atlassian.com/display/DOC/Creating+a+Lowercase+Page+Title+Index
How to find the ones that need this:
- See SQL query of query execution planner for slow queries
- SQL Profiler - looking at prepare statements
- For ideas, also see http://jira.atlassian.com/browse/CONF-10030

From 2008
Also Confluenece 2.7 adds all but the following to SQL SERVER 2005 from the suggestions above.

CREATE INDEX CONTENT_IDX4 on CONTENT( CONTENTTYPE );

CREATE INDEX CONTENT_IDX7 ON CONTENT (PREVVER);

CREATE INDEX CONTENT_IDX9 ON CONTENT (CONTENT_STATUS);

CREATE INDEX CONTENT_IDX8 ON CONTENT (PAGEID);

CREATE INDEX CONTENT_IDX11 on CONTENT( lower(TITLE) );

Here is the code for SQL server:

alter table content add title_lower as lower(Title)
create index conftitle_lower on Content(title_lower)

alter table attachments add title_lower as lower(title);
create index att_title_lower on attachments(title_lower);

alter table extrnlnks add url_lower as lower(URL);
create index elnks_url_lower on extrnlnks(URL_LOWER);