If the implementation of SQLite you are using overrides the LIKE operator (as more than a few do), then SQLite will not make use of an index on the column in question. Use the GLOB operator instead.
For example, I have a lexicon containing 263,000 words: select count(*) from lexicon where spelling like 'a%' // 552 ms on first run and then 355ms on second and subsequent runs select count(*) from lexicon where spelling glob 'a*' // 110 ms on first run and then ~10ms on second and subsequent runs Alternatively: select substr(spelling,1,1), count (*) from lexicon group by substr(spelling,1,1) order by substr(spelling,1,1) // ~3500 ms on first run and then ~2400 ms on second and subsequent runs Of course, if your lexicon is static, you could create an ancillary table of first letters and their corresponding counts. Regards Tim Romano 2010/4/25 Alberto Simões <hashas...@gmail.com> > Hello > > I am running on the fly a query to count the number of words starting > with one of the 26 letters. > > I am doing the usual SELECT COUNT(term) from dictionary WHERE normword > LIKE "a%" (for the 26 letters) > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
