Proposal for programmatically setting index options
Monday, 13 May 2013 · 2 messages · last reply 14 May 2013
AB
Andy Bunce13 May 2013 12:56 #
Hi,
The index settings for a database can make a huge difference to query performance. I have been playing with a database where the time for a query using distinct-values went from 4secs to 4ms by increasing the
MAXCATS value. Currently it is rather fiddly to change these values, particularly to this programmatically. with different values per database.
I would find it useful there was an easier way to do this. A quick suggestion as to how this might be done:
db:optimize could be extended to taker a third argument that matched the format of in indexes portion of the db:info command<indexes>
<uptodate>true</uptodate>
<textindex>ON</textindex>
<attributeindex>ON</attributeindex>
<fulltextindex>OFF</fulltextindex>
<updindex>OFF</updindex>
<maxcats>100</maxcats>
<maxlen>96</maxlen>
</indexes>e.g
db:optimize("mydb",fn:true(),$indexopts)
This would rebuild "mydb" using the settings specified in $indexopts. The extra argument could also be added to db:create.
/AndyCG
Christian Grün14 May 2013 12:01 #
Hi Andy,
thanks for your proposal; it sounds well-wrought to me, and I’m pretty sure we’ll implement it soon in this or a slightly similar way. I’ve added a GitHub issue to memorize your idea.
Christian
Quoted text · Andy Bunce · 38 lines
On Mon, May 13, 2013 at 12:56 PM, Andy Bunce <bunce.andy at gmail.com> wrote: > Hi, > > The index settings for a database can make a huge difference to query > performance. I have been playing with a database where the time for a query > using distinct-values went from 4secs to 4ms by increasing the MAXCATS > value. Currently it is rather fiddly to change these values, particularly to > this programmatically. with different values per database. > > I would find it useful there was an easier way to do this. A quick > suggestion as to how this might be done: > > db:optimize could be extended to taker a third argument that matched the > format of in indexes portion of the db:info command > > <indexes> > <uptodate>true</uptodate> > <textindex>ON</textindex> > <attributeindex>ON</attributeindex> > <fulltextindex>OFF</fulltextindex> > <updindex>OFF</updindex> > <maxcats>100</maxcats> > <maxlen>96</maxlen> > </indexes> > > e.g > db:optimize("mydb",fn:true(),$indexopts) > > This would rebuild "mydb" using the settings specified in $indexopts. The > extra argument could also be added to db:create. > > /Andy > > _______________________________________________ > BaseX-Talk mailing list > BaseX-Talk at mailman.uni-konstanz.de > https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk >
Written for BaseX 7.6. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery