http://trac.edgewall.org/attachment/ticket/10665/10665-sql-intersects.diff

100K_8

With 100K tickets and eight custom fields, in the current system:

{{{
2013-02-04 11:27:23,508 Trac[web_ui] INFO: needed 2.1167910099 seconds for searching terms '[u'nardine']'
2013-02-04 11:27:28,881 Trac[web_ui] INFO: needed 2.04926395416 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:27:34,073 Trac[web_ui] INFO: needed 2.05887007713 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:27:37,980 Trac[web_ui] INFO: needed 2.05345582962 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:27:41,786 Trac[web_ui] INFO: needed 2.06689596176 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

With the patch:

{{{
2013-02-04 11:27:59,112 Trac[web_ui] INFO: needed 2.13388609886 seconds for searching terms '[u'nardine']'
2013-02-04 11:28:06,713 Trac[web_ui] INFO: needed 4.24195885658 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:28:44,086 Trac[web_ui] INFO: needed 6.27078199387 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:28:57,239 Trac[web_ui] INFO: needed 8.02531099319 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:29:09,464 Trac[web_ui] INFO: needed 9.62579298019 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

10K_2

With 10K tickets and two custom fields, in the current system:

{{{
2013-02-04 11:08:07,292 Trac[web_ui] INFO: needed 0.0907759666443 seconds for searching terms '[u'nardine']'
2013-02-04 11:08:14,461 Trac[web_ui] INFO: needed 0.0840229988098 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:08:17,641 Trac[web_ui] INFO: needed 0.0810270309448 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:08:21,325 Trac[web_ui] INFO: needed 0.0830581188202 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:08:23,157 Trac[web_ui] INFO: needed 0.0839810371399 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

With the patch:

{{{
2013-02-04 11:08:41,866 Trac[web_ui] INFO: needed 0.0833230018616 seconds for searching terms '[u'nardine']'
2013-02-04 11:08:50,009 Trac[web_ui] INFO: needed 0.160645961761 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:08:54,326 Trac[web_ui] INFO: needed 0.239274024963 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:08:59,136 Trac[web_ui] INFO: needed 0.319802045822 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:09:03,038 Trac[web_ui] INFO: needed 0.381479978561 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

10K_8

With 10K tickets and eight custom fields, in the current system:

{{{
2013-02-04 11:19:13,043 Trac[web_ui] INFO: needed 0.212724924088 seconds for searching terms '[u'nardine']'
2013-02-04 11:19:17,166 Trac[web_ui] INFO: needed 0.204668045044 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:19:20,355 Trac[web_ui] INFO: needed 0.211205005646 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:19:23,201 Trac[web_ui] INFO: needed 0.208470106125 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:19:24,968 Trac[web_ui] INFO: needed 0.208981990814 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

With the patch:

{{{
2013-02-04 11:19:36,802 Trac[web_ui] INFO: needed 0.214637994766 seconds for searching terms '[u'nardine']'
2013-02-04 11:19:40,881 Trac[web_ui] INFO: needed 0.416366815567 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:19:44,719 Trac[web_ui] INFO: needed 0.615374088287 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:19:48,750 Trac[web_ui] INFO: needed 0.798229932785 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:19:52,395 Trac[web_ui] INFO: needed 0.970859050751 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

10K_8_pagelength

With 10K tickets and eight custom fields that are all filled in with page-length text instead of paragraph-length text, in the current system:

2013-02-04 11:32:38,448 Trac[web_ui] INFO: needed 0.570473194122 seconds for searching terms '[u'nardine']'
2013-02-04 11:32:44,060 Trac[web_ui] INFO: needed 0.546642065048 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:32:48,553 Trac[web_ui] INFO: needed 0.545171976089 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:32:50,451 Trac[web_ui] INFO: needed 0.546708106995 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:32:52,354 Trac[web_ui] INFO: needed 0.55531001091 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

With the patch:

{{{
2013-02-04 11:33:03,833 Trac[web_ui] INFO: needed 0.576239109039 seconds for searching terms '[u'nardine']'
2013-02-04 11:33:07,547 Trac[web_ui] INFO: needed 1.0984609127 seconds for searching terms '[u'nardine', u'reminiscency']'
2013-02-04 11:33:12,193 Trac[web_ui] INFO: needed 1.64014315605 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran']'
2013-02-04 11:33:15,987 Trac[web_ui] INFO: needed 2.09856700897 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize']'
2013-02-04 11:33:20,322 Trac[web_ui] INFO: needed 2.54767203331 seconds for searching terms '[u'nardine', u'reminiscency', u'Niagaran', u'pyramidize', u'monasterially']'
}}}

fill_db.py

from trac.env import Environment
from trac.ticket.model import Ticket
from trac.tests.contentgen import *
import sys

env = Environment(sys.argv[1])
for i in xrange(int(sys.argv[2])):
    ticket = Ticket(env)
    ticket['summary'] = random_sentence()
    ticket['description'] = random_paragraph()  ## or random_page()
    for custom_field in sys.argv[3:]:
        ticket[custom_field] = random_paragraph()  ## or random_page()
    ticket.insert()
    
    
## trac-admin /path/to/env initenv
## turn on logging and add some custom fields of type text
## python fill_db.py /path/to/env 10000 custom1 custom2 custom3 custom4 custom5