Greetings, I am looking for tips, tricks and general information, both for personal usage (I'd like to move as many as possible of my SOHO-related databases to SQLITE) and for an online article, about SQLite3 query optimization tricks. I have already found and will study these pages:
http://www.sqlite.org/optoverview.html http://katastrophos.net/andre/blog/2007/01/04/sqlite-performance-tuning-and-optimization-on-embedded-systems/ http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html however, the third link is quite old and referring to SQLite 2.8.6, the second is for embedded systems. My main interest, instead, is about SQLite3 used from PHP in web servers or from OpenOffice, Kexi, etc... on Linux desktops. A few examples of the kind of queries I'd like to (learn how to) optimize first: - search of strings in text fields (both sub-words and whole words) - calculation of moving average of a floating field, eg if a table is like this: MONTH SALES_TOTAL 2007-01 500 2007-02 1059.3 ... 2008-06 439 2008-07 605.52 what is the fastest SQLITE3 query to read that table and return another table with the average sales over the last 12 months: 2008-05 (total sales from 2007-06 to 2008-05)/12 2008-06 (total sales from 2007-07 to 2008-06)/12 2008-07 (total sales from 2007-08 to 2008-07)/12 but any query optimization trick is welcome, really. Thank you in advance for any feedback, Marco -- Your own civil rights and the quality of your own life heavily depend on how software is used *around* you: http://digifreedom.net/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
