--- Dan Kennedy <[EMAIL PROTECTED]> wrote: > > > On Thu, 2007-03-01 at 15:13 -0800, jp wrote: > > Hi, I have a custom collation sequence (e.g. > > mycollate). Are there any advantages in terms of > > performance of declaring this collation at the > table > > level, instead of just at the index level? > > > > For example, if I have: > > > > CREATE TABLE people AS ( > > country_id char(02), > > lastname varchar(100), > > phone varchar(50) > > ); > > > > CREATE UNIQUE INDEX people_mycollate on people ( > > country_id, > > lastname COLLATE mycollate > > ); > > - - - - - - > > ...will the following use the index (about 500k > recs)? > > - - - - - - > > SELECT lastname FROM people > > WHERE country_id='US' AND lastname>'A' > > ORDER BY country_id,lastname COLLATE mycollate > > LIMIT 100; > > - - - - - - > > SQLite will use the index to implement the > country_id='US' clause, > and the ORDER BY, but not the lastname>'A' clause. > This is because > the lastname>'A' doesn't use the "mycollate" > collation. > > If the COLLATE clause was specified as part of the > table > definition, then lastname>'A' would be a "mycollate" > comparison > and the index would be used for this too. > > Dan.
So, is creating an index with "collate" useless if you didn't specify the collate at the table level? Is there any other way to force sqlite to use the index with the collate? jp ____________________________________________________________________________________ Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
