On Fri, 15 Apr 2016 22:53:57 +0100 Simon Slavin <slavins at bigfraud.org> wrote: > On 15 Apr 2016, at 10:05pm, Cecil Westerhof <cldwesterhof at gmail.com> > wrote: > > * SQLite datatypes and how SQLite decides which datatype you want > * SQLite uses affinities not column types > * thinking you should index each column instead of indexes for queries > * consequences of various PRAGMAs which might appear to speed SQLite > up > * consequences of the two different journal types (including file > size)
That's a nice list. I might add using LIMIT and ORDER where MIN would serve better. > The most common mistake which leads to posts to this list is -- still > the winnah after five years ! -- premature optimization. I can't > believe how much coding time and software complication is wasted by > people who spend hours on multi-thread, queueing and partitioning > code instead of redesigning their schema or thinking through how > indexes work. Yes, except it's not *premature* optimization, is it? It's misdirected optimization, worrying about the wrong thing because not measuring. Knuth's message is often misunderstood and oversimplified. Perhaps he could be better paraphrased as "don't sweat the small stuff", because he was most certainly not saying, "just make it work and worry about design choices later". He did not mean we should ignore O(1) versus O(n^2) choices when those choices matter. His advice doesn't imply that orders of magnitude are unworthy of our concern. The best database performance advice I ever got still applies: think about how the DBMS will traverse the data, and minimize the work it will do. It's all about I/O. No number of additional threads will bring the data into memory sooner. --jkl
