2008/9/9 Igor Tandetnik <[EMAIL PROTECTED]>: > Bruno Moreira Guedes > <[EMAIL PROTECTED]> wrote: >> I can split my 'something' in st0, st1, st2, stN... I have a '.' >> betwen this values. But the number of "st"s is undefined. I tried to >> do "the necessary" number comparsions, appending tokens: >> >> SELECT fields FROM sometable WHERE field = 'st0.st1.st2.st3' OR field >> = 'st1.st2.st3' OR field = 'st2.st3' OR field = 'st3'; >> >> Unfortunately, even creating a INDEX, the performance was decreased >> instead of being increased. > > Try formulating it this way: > > SELECT fields FROM sometable WHERE field IN ( > 'st0.st1.st2.st3', 'st1.st2.st3', 'st2.st3', 'st3'); > > > As the number of pieces grows, it might be better to create and fill a > temporary table with suffixes, then do > > SELECT fields FROM sometable WHERE field IN > (select suffix from suffixes); > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
Hi all!! Dennis, Thank you by your idea. I've been made some testing, and it seems to be the better solution. Unfortunately, my table has three fields(including the special rowid field), two integers and a varchar. The amount of data(about 500.000 rows) recently added increased 32Mb on database's file. So, I think maintaining a 'reverse copy' of varchar field will increase too many the database's file size - considering it'll grow about 10x in a few days. Igor, Thank you also. Using the 'IN' really solved the problem. The query runs fast. It'll require some 'manual string processing', because my application will have to generate the "list of something's tails", but as there's no big number of tails, it isn't a problem, and solve the needs by now. Thank you all people. It's hard to develop database applications with little database knowledge. Without you I couldn't make it works. Bruno M Guedes _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
