Igor Korot wrote: > 1. I am working with C++ using C API to access the DB. > What I did was: > > upon startup - read the data in the std::vector<>, then use > std::sort() to sort this vector appropriately. > > Now, today it hit me that I can use "ORDER BY" SELECT clause to > retrieve data as pre-sorted. > > What is best practice here? Should I use "ORDER BY" or leave my code as it is?
If your code works, it works. In general, you should use ORDER BY, if possible. (And there might be cases where it's not possible because std::sort behaves differently.) > 2. > sqlite> EXPLAIN QUERY PLAN SELECT players.playerid FROM players WHERE > players.na > me = "Igor Korot" AND players.age = 25 AND players.teamid = (SELECT teamid > FROM > teams WHERE teams.name = "SF"); > selectid|order|from|detail > 0|0|0|SCAN TABLE players (~1000 rows) > 0|0|0|EXECUTE SCALAR SUBQUERY 1 > 1|0|0|SEARCH TABLE teams USING AUTOMATIC COVERING INDEX (name=?) (~7 rows) > > players table does not have any indexes yet. > What index needs to be created? (name, age, teamid) IIUC, right? Yes; all three lookup columns are in the index, and the column with the highest selectivity comes first. If you add playerid to the index, you have a covering index. This index speeds up this particulary query, but whether it is needed or worthwile for your entire application is another question. If you add such an index for every query, you might get so many indexes that updating them for every change in the base table can become too slow. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
