On 20 Mar 2012, at 6:11pm, Tim Morton <[email protected]> wrote:
> I may try this, but the topics are usually just one word each so I doubt an > index could reduce it much. Max's trick is something that takes advantage of how SQLite works entirely. If you do a SELECT like SELECT id,topics FROM dictionary ORDER BY topics and there's an index like CREATE INDEX dti ON dictionary (topics,id) then SQLite will use that index to find the right order to retrieve the records in. But having figured out what rows it needs it realises that it has all the information -- both the id and the topic -- right there in the index. So it doesn't bother then looking up the TABLE data to fetch the other columns, it just returns all the information you asked for right from the index it's already looking at. It's fast and, because the index contains only the data you want, it's equivalent to making up a separate table which has just the id and topics columns. I forgot about that trick when I replied but Max didn't. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
