On 16 Feb 2011, at 10:46pm, David M. Cotter wrote: >> 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?
Instead of making up LIKE clause for each field showing, concatenate up a search field based on which fields are showing. Then you just need a single LIKE. For example instead of >> 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 you could have >> SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE (p.plID = >> 99662 AND CONCATENATE(s.name,s.arts,s.pUSD,s.pbls,s.genr) LIKE "％love％") >> ORDER BY s.pbls ASC, s.name ASC, s.albm ASC, p.piIx ASC > well, not really, it's just a loop to compose the SQL statement. it's > already written. But it creates a very long SQL statement which takes SQLite a long time to execute. >> 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. So make up your CONCAT or TRIGGER or VIEW appropriately for each TABLE. >>> 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? Did you read the page at the URL I gave ? It answers the question. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
