Hello, Hugo, Regarding: "I have a table with 726.000 registers."
1) I assume that you mean what others call "rows" correct? (and not columns, I hope) 2) Regarding: "SELECT COUNT(*) FROM MyTable << is very slowly" As I understand it, that should be as fast as SELECT COUNT (RowId) as of late 2013. What version of sqlite are you using? You can make SELECT COUNT(*) FROM MyTable be almost instantaneous, at the cost of a little effort and a slight slowdown with inserts and deletes, by maintaining the current row count in the database, and creating TRIGGERs to keep it current. http://stackoverflow.com/questions/2869135/sqlite-trigger-to-update-summary-counts 3) Regarding: "SELECT COUNT(RowId) FROM MyTable WHERE a LIKE '%abc%' ORDER BY PrimaryIndex << is FAST with more registers, but is SLOWLY with 726000 registers" You don't mention whether you have an index defined on column "a", but even if you did, using LIKE with a wildcard (%) at the beginning means that SQLite cannot use an index and must scan the entire table. Perhaps: a) If you only have to search for a few known strings like %abc%, then you could check for matches at the time rows are inserted, and update a new column that signifies, e.g., "contains a match for %abc%" and create indexes on those new columns. b) If the above is not feasible, you might want to look into SQLite's FTS feature (full text search). 4) Regarding: "Which is the fastest way to select 23 registers that meet a condition ?????" Simon Slavin answered that in his reply to you two days ago: SELECT COUNT(*) FROM MyTable WHERE a = b and, of course, you would expect a great speed increase by having either column "a" or column "b" indexed, but remember the caveat for LIKE expressions. 5) In general: -- Consider whether defining more sqlite cache would help. -- Be sure you have useful indexes defined (and remove non-useful ones. Use EXPLAIN QUERY PLAN) -- Be sure to use TRANSACTIONs around multiple statements to be executed as a unit -- this won't speed up individual SELECTS that you asked about, but can make such a tremendous speedup for other things -- such as multiple inserts -- that I'm mentioning it. Regards, Donald _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
