Performance of the column search in the pages and assets app when we tested it on our large repository (100.000+ nodes) was not really usable. Analysis showed that the queries generated by JcrQueryBuilder did not really perform.

By experimenting the biggest improvement was to reduce the use of likes, and if used only with a postfix wildcard (ie 'test%' instead of '%test%') as this reduces the number of hits we included an contains query as well with little to no performance hit (The resulting query is quite similar to what Magnolia 5 used in the search view).

Another thing that increased performance to remove the use of wildcards on columns that have limited values (ie mgnl:template and such).

A curious one (which we fully blame on JackRabbit) was the the jcrName wildcard query (ie '%nodename%). A comparison with the queries generated by Magnolia 5 revealed that adding a property existence query increased performance significantly. So the difference between one without and one without on our repository is really something:
SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page' OR t.[jcr:primaryType] = 'mgnl:variants' OR t.[jcr:primaryType] = 'mgnl:variant') AND (LOWER(LOCALNAME(t)) LIKE '%test%' )
546 nodes returned in 19210ms
SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page' OR t.[jcr:primaryType] = 'mgnl:variants' OR t.[jcr:primaryType] = 'mgnl:variant') AND (LOWER(LOCALNAME(t)) LIKE '%test%' OR t.['xxxx'] is not null)
546 nodes returned in 426ms