>> iTunes has "update search results as you type" speed even when you have a >> hundred thousand songs and you're searching on a partial string on all meta >> data columns. >> >> how on earth do they do that? >> >> i'm under the impression it uses CoreData, which in turn uses SQLite under >> the hood. >> >> how can i make my partial string searches searches thru multiple columns go >> that fast? FTS seems to imply it can make things blazing fast. is that >> really true? > > For just a hundred thousand tracks, most of which don't have anything in > subsidiary fields like 'Composer', SQLite can do it that fast itself. really?
using substring search using [LIKE '%foo%'] ?? cuz for me it's taking about 4 seconds to do a search. i have an itunes-like app and i have lots of columns. here's a sample query on 40k songs: SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE p.plID = 99662 AND (s.name LIKE "%love％" OR s.arts LIKE "％love％" OR s.pUSD LIKE "％love％" OR s.pbls LIKE "％love％" OR s.genr LIKE "％love％") ORDER BY s.pbls ASC, s.name ASC, s.albm ASC, p.piIx ASC query time: 00:3.61 note it requires column searching because the user may hide or show any column and i only want to search columns that are showing. it gets more complicated when they user types multiple words: SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE p.plID = 99662 AND (s.name LIKE "％love％" OR s.arts LIKE "％love％" OR s.pUSD LIKE "％love％" OR s.pbls LIKE "％love％" OR s.genr LIKE "％love％") AND (s.name LIKE "％u2％" OR s.arts LIKE "％u2％" OR s.pUSD LIKE "％u2％" OR s.pbls LIKE "％u2％" OR s.genr LIKE "％u2％") ORDER BY s.arts ASC, s.name ASC, s.albm ASC, p.piIx ASC query time: 00:3.63 am i doing it wrong? how can i speed this up? > But SQLite has an extra chunk called FTS4 which pre-indexes the contents of > the database for partial text searches like that: yes i know about that but i understand it doesn't speed up stuff if you're doing substring searching. eg: MATCH 'foo*' <-- fast MATCH '*foo*' <-- slow Right? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
