db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: Poor query optimizer choices is making Derby unusable for large tables
Date Sat, 12 Nov 2005 22:04:54 GMT
On Saturday 12 November 2005 10:28, Michael Segel wrote:

> 	CREATE TABLE tblSearchDictionary
> 	(
> 		 objectId		INT NOT NULL,
> 		 objectType 	INT NOT NULL,
> 		 wordLocation	INT NOT NULL,
> 		 word 		VARCHAR(64) NOT NULL,
> 		CONSTRAINT CONSd0e222 UNIQUE (objectId,objectType,word,wordLocation)
> 	);
>
> > This table has an index on each of the four columns, it also has the
> > unique index across all four columns as defined above:
>
As a follow up...
Change the constraint order to UNIQUE(objectId, word, wordLocation, 
objectType);

You probably can drop the index I asked you to create if you make this mod. 
Then your query should use the unique constraint's "backing index"...

HTH.

-G


-- 
Michael Segel
Principal 
MSCC

Mime
View raw message