On 31 Jan 2016, at 4:39pm, Yannick Duch?ne <yannick_duchene at yahoo.fr> wrote:
> I saw a page (can't retrieve the URL) suggesting to order table columns by > names. It was strange to me, as I had the idea of a hierarchical access for > tables access. But I though ?there must be a good reason for them to say > this?. Then in an SQLite page [1], there was a suggestion to avoid index > containing the same data as a wider index. So after these two things, I tried > to imagine ways of setting up an index so that this makes sense: I though a > multi?column key could be accessed by any column, using fragments whose > content are ordered. > > Precisely with the case of your example, I though the "name" column would be > partitioned into individually sorted parts. While it was also contradicted by > the fact adding a index on a single column of a multi?column primary key, > could help grouping (although later again, there was another surprise > contradicting this too). Ignore all the above. There are rare situations where they're useful but the situation you're in is helped far more by using the phonebook analogy earlier posters used than by trying to use the above. Think about pure SQL, and about making one ideal index for each SELECT command, and you'll get very good results. Work out what you want the SELECT (or UPDATE, etc.) to do. Then work out the command. Then work out the index which would be ideal to help the command do its thing. That's the best way to get fast SQL. Optimizing for SQLite peculiarities (some of which no longer apply because inner workings of SQLite have changed since the article was written) is useful only very rarely. Simon.
