On 16 Feb 2011, at 8:41pm, David M. Cotter wrote: >> 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
Good grief, no that's going to get slower the more fields you have. And more annoying to program. You make a composite field which has all those fields concatted together, either manually or using a TRIGGER or a VIEW. Then you only have to search in one field. > 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. Oh. That's your problem. iTunes doesn't do that. But you could manually filter the results of the first search, which should still be faster than your original formulation. Or you could manually compose your CONCATENATE parameters based on which fields 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? What do you do if they put three words in the search field ? Or seven ? Do an initial search on the first search term, then search the results of that for the second term, etc.. There are exceptions to the rule, but any time you find your self hand-hacking the SQL command for user-input you should be able to find a more general solution that works in more cases and is probably faster. >> 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 <http://www.sqlite.org/fts3.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
