Hi, recently I finally started experimenting with virtual tables and there's at least one thing I can not understand.
As I see xBestIndex/xFilter were developed to allow fast searching if the implementation is able to do this. But there's also sql language that allows very exotic queries. Some of them may be recognized by the implementation, some not. If the former, one just can rely on sqlite double checking and just do full scan. But there are also cases when it looks like recognition is not possible. For example SELECT * FROM vtest where id > random() in this case xBestIndex just assumes some constant as the expression, so the one who implements just can't detect probably unresolved query and thinks that it can search quickly (binary search, for example). The call to xFilter just passes first random value and sqlite will never call it again for the same enumeration. So xFilter thinks this is the constant value used in the query and jumps to the first correct row row never planning to jump back. But this is actually a misleading action since in real world sqlite calls random on every row and the rows bypassed are actually important and can be evaluated to true. I mentioned random(), but there may be other cases, for example when other fields are part of expressions. So, the main question: is it possible to detect simple expressions that can be correctly resolved by quick searching? I know that I can always rely on sqlite double-checking and always do full scan. But theoretically for large datasets one should at least think about some optimization. Thanks, Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
