Just to complete the thread, I decided for the following: SELECT substr(normalized,1,1) AS letter, COUNT(*) from entry group by letter order by letter;
Thank you ALL! 2010/4/26 Alberto Simões <hashas...@gmail.com>: > Hello > > Thank you all for the answers. > > On Mon, Apr 26, 2010 at 12:59 PM, Black, Michael (IS) > <michael.bla...@ngc.com> wrote: >> When you say "running on the fly" do you mean running from an sqlite3 >> command prompt? > > I mean somebody will query it and will be waiting for the answer. > >> Or are you doing this in some other programming language? > > Perl > >> Why in the world would you use a database to do this? > > Probably I am not using a database for this, but for something else, > and I want to add a feature to let users read some statistics. > >> >> >> 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) >> >> normword is the term normalized without accents and the like >> >> >> Is there any way to make this query faster? It is taking about 10 >> second for 140K entries. >> >> One idea is to add a column named 'letter' and SELECT COUNT(letter) >> from dictionary WHERE letter = 'a'. >> But are there other solutions? >> >> Thanks >> -- >> Alberto Simões >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > > -- > Alberto Simões > -- Alberto Simões _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users