>> 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 yes i suspected as much
> And more annoying to program well, not really, it's just a loop to compose the SQL statement. it's already written. query.ssprintf( "SELECT p." CSQL_Key_PLI_INDEX " FROM " CSQL_Table_PLAYLIST " p" " JOIN " CSQL_Table_SONG " s" " ON p." CSQL_Key_SONG_ID " = s." CSQL_Key_SONG_ID " WHERE p." CSQL_Key_PLAYLIST_ID " = %d", (int)filterData.i_plP->GetID()); BOOST_FOREACH(const CStringFilter& filter, filterData.i_stringVec) { bool first_timeB = true; query.append(" AND ("); BOOST_FOREACH(OSType key, filterData.i_columns) { if (first_timeB) { first_timeB = false; } else { query.append(" OR "); } query.append("s."); query.append(OSTypeToString(key)); query.append(" LIKE \"%"); query.append(filter.utf8.c_str()); query.append("%\""); } query.append(")"); } > 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. but different playlists have different columns. for example a disc burn playlist doesn't have a price column and a music store playlist doesn't have a track number column. > Or you could manually compose your CONCATENATE parameters based on which > fields are showing. i'm not sure what you mean. can you give an example? >> 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 ? the loop takes care of it. eg: SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE p.plID = 99662 AND (s.name LIKE "%this%" OR s.arts LIKE "%this%" OR s.pUSD LIKE "%this%" OR s.pbls LIKE "%this%" OR s.genr LIKE "%this%") AND (s.name LIKE "%is%" OR s.arts LIKE "%is%" OR s.pUSD LIKE "%is%" OR s.pbls LIKE "%is%" OR s.genr LIKE "%is%") AND (s.name LIKE "%a%" OR s.arts LIKE "%a%" OR s.pUSD LIKE "%a%" OR s.pbls LIKE "%a%" OR s.genr LIKE "%a%") AND (s.name LIKE "%many%" OR s.arts LIKE "%many%" OR s.pUSD LIKE "%many%" OR s.pbls LIKE "%many%" OR s.genr LIKE "%many%") AND (s.name LIKE "%term%" OR s.arts LIKE "%term%" OR s.pUSD LIKE "%term%" OR s.pbls LIKE "%term%" OR s.genr LIKE "%term%") AND (s.name LIKE "%search%" OR s.arts LIKE "%search%" OR s.pUSD LIKE "%search%" OR s.pbls LIKE "%search%" OR s.genr LIKE "%search%") ORDER BY s.arts ASC, s.name ASC, s.albm ASC, p.piIx ASC "this is a many term search": 00:4.31 > Do an initial search on the first search term, then search the results of > that for the second term, etc.. it seems it's actually faster to do a multiterm search as above: SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE p.plID = 99662 AND (s.name LIKE "%this%" OR s.arts LIKE "%this%" OR s.pUSD LIKE "%this%" OR s.pbls LIKE "%this%" OR s.genr LIKE "%this%") ORDER BY s.arts ASC, s.name ASC, s.albm ASC, p.piIx ASC "this": 00:5.48 > 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. so, is the above considered "hand hacking" ? it's all done programmatically. >>> 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 this is actually the most important question: can someone chime in on that? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users