Thanks all! This worked (winxp, sqlite 3.3.13) and does use the index: > SELECT lastname FROM people > WHERE country_id='US' AND lastname COLLATE > mycollate >'A' > ORDER BY country_id,lastname COLLATE mycollate > LIMIT 100;
jp --- Dennis Cote <[EMAIL PROTECTED]> wrote: > Dan Kennedy 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. > > > > > I'm not sure if it is all implemented yet, but > Richard has checked in > some changes to the collation handling. > > The query should use the index as created if the > comparison is changed > to use the specified collation like this. > > SELECT lastname FROM people > WHERE country_id='US' AND lastname COLLATE > mycollate >'A' > ORDER BY country_id,lastname COLLATE mycollate > LIMIT 100; > > In this case you are using explicit collations > everywhere the lastname > is used. > > If you change the table definition then mycollate > will be the default or > implicit collation for that field. The OP said that > he wanted to use > different collations for this field in different > queries, but that > doesn't mean that he couldn't use mycollate as the > default collation and > apply any others that he needed using explicit > collation clauses like > the query above. > > HTH > Dennis Cote > > ----------------------------------------------------------------------------- > To unsubscribe, send email to > [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > ____________________________________________________________________________________ Looking for earth-friendly autos? Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
