Keith & Simon, Excellent explanations. Thank you.
-- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: Saturday, July 04, 2015 1:14 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Slow real world performance - Any suggestions > please (warning long) > > > On 4 Jul 2015, at 5:46pm, William Drago <wdrago at suffolk.lib.ny.us> > wrote: > > > Clearly, in this case, using COLLATE NOCASE in the table definition > is > > the right thing to do. Under what conditions would using it in the > index instead be the right thing to do? > > It's rare. Sometimes you have a column where case normally does > matter, but occasionally want to search case-insensitive. This might > happen in a field where acronyms and initialisms are used a lot and you > need to distinguish between them. For example, an English Language > dataset might normally need to distinguish between 'ACID' and 'acid' > but you might want to enable a fast searching facility, without your > users having to be fussy about typing capital letters. > > It's also possible to do it the other way: define the column as COLLATE > NOCASE but have an index include the column COLLATE BINARY. Or even > COLLATE RTRIM. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
