>> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
