I would create a tagList table (integer tagID, string tagName, unique(tagName))
Before performing your batch of inserts, query the tagList table (integer tagID, string tagName), and generate a map (key on tagName, value of tagID). For each tag you want to insert, see if it exists in the map. If it doesn't, insert first into tagList table and get the tagID of that new entry and update your map. Perform your insert with the tagID instead now. If it does exist, use the value of the tagName key in your map. I chose a map (or some similar implementation), because the lookup should be quick, and you won't need to query the DB for each new tag during your batch of inserts. On Mon, Feb 7, 2011 at 2:32 PM, Yuzem <naujnit...@gmail.com> wrote: > > > Petite Abeille-2 wrote: > > > > Hey... sounds like IMdb :P > > > Yes, I'm coding a http://yuzem.blogspot.com/p/figuritas-screenshots.html > movie manager that grabs the info from imdb. > > > Petite Abeille-2 wrote: > > > > In any case, as mentioned by Michael Black, you might benefit greatly by > > normalizing your table and indexing the foreign key: > > > > http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L401 > > > > I thought sqlite didn't handle foreign keys correctly, I am using triggers > to automatically delete tags when a movie gets deleted. > If I use a foreign key will it automatically remove the tag if the movie > gets deleted? > Anyway, to use integers in the "tags" table is very complicated because I > will have to assign the corresponding number to each tag that I insert and > I > have to insert lots of keywords for every movie. > Does sqlite has any function to convert a text string into an unique > number? > > -- > View this message in context: > http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30867411.html > Sent from the SQLite mailing list archive at Nabble.com. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
