Hi, Thank you. In that case, if I create an index for (F1, F2, F3), then the next time when I invoke SELECT statement like this:
SELECT * FROM MyTable ORDER BY F1, F2, F3; Then SQLite will utilize the index automatically, is that correct? If I use SELECT * FROM MyTable ORDER BY F1, F2; Then the above index cannot be utilized and the speed will be slow again? Thanks > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: Wednesday, February 24, 2016 11:27 AM > To: SQLite mailing list > Subject: Re: [sqlite] Order by multiple columns > Importance: High > > > On 24 Feb 2016, at 3:15am, admin at shuling.net wrote: > > > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); > > > > Now if I want to select from MyTable, and sort the result based on F1 > > (ascendant), and for two records with same F1, then sort based on > > F2(ascendant), then sort based on F3(ascendant) should I use the > > following > > : > > > > SELECT * FROM MyTable ORDER BY F1, F2, F3? > > Yes. Why not try it ? Download the SQLite shell tool from the SQLite > download page and experiment with it. Instructions here: > > <https://www.sqlite.org/cli.html> > > > Moreover, since the sort will be performed frequently, to improve the > > performance, I want to add a field F4, then update the table so that > > all the records will have a unique F4 value which is the order > > sequence based on the above order by clause, thus if I want to select > > again, I do not need to use ORDER BY F1, F2, F3 any more, instead, I can use: > > > > SELECT * FROM MyTable ORDER BY F4 > > > > Is that feasible? If yes, how do to that? > > You don't do it like that. Instead you create an index: > > CREATE INDEX m_f1f2f3 ON MyTable (F1, F2, F3) > > This does something similar to what you were trying to do with F4, but it > means that the database has a copy of that table pre-sorted into the order > your SELECT needs. The next time you execute the SELECT SQLite will > automatically spot that it has an idea index already prepared and it will use it. > > You will not notice much difference in the amount of time taken with your > sample database because it has only 7 rows, but once you have 7000 rows > the figures should be easy to tell apart. > > An index is automatically updated every time the data in the table changes. > In other words, once you have created it you can forget about it. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
