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 want to separate the table definition from the > search/query/sort order, to have the flexibility of > creating/dropping indexes as needed for different > collations depending on the user's locale. > > jp > > > > > ____________________________________________________________________________________ > The fish are biting. > Get more visitors on your site using Yahoo! Search Marketing. > http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
