Glad the updated statistics worked for you. The specificity of messageId seemed extremely high, and given only 1 million rows should have been returning sub-second results. You don't need indexes on either of the other two fields, they won't improve performance and will slow insertion speed. Brett
On Fri, Sep 18, 2009 at 9:31 AM, Andrew Bruno <[email protected]>wrote: > Hey all, > > Sorry been flat chat for a while, but I gotta say that the query > improved dramatically when the stats updated. > > When I created the index, I didnt wait for the stats to be updated. > > I came back the next day, and the query was running in sub seconds. > Now ExchangeSync can run well again on this 3G database. > > I want to thank everyone for the awesome support, including some of > those left or right field ideas! > > Cheers > Andrew > > > On Thu, Sep 17, 2009 at 7:26 PM, Knut Anders Hatlen <[email protected]> > wrote: > > Brett Wooldridge <[email protected]> writes: > > > >> You can force Derby to update statistics with this command: > >> > >> alter table <table-name> compress [sequential] > >> > >> Note this command itself might take a long time -- dozens of minutes -- > but in > >> a system like yours you could get away with running it once or month or > so at > >> some off-peak time. > > > > In Derby 10.5 you have a cheaper way of updating the index cardinality > > statistics. This statement will update the statistics for all the > > indexes on columns in MYSCHEMA.MYTABLE without doing an expensive > > compress: > > > > CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('MYSCHEMA', 'MYTABLE', NULL) > > > > http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html > > > > -- > > Knut Anders > > >
