Thanks, Clemens. Some of my comments as follows 2015-06-17 1:23 GMT-07:00 Clemens Ladisch <clemens at ladisch.de>:
> Jerry wrote: > > With xBestIndex and xFilter, we can pass the constraint information > (e.g., > > those from WHERE clause) to virtual table (through struct > > sqlite3_index_info), so that we can locate the cursor to narrow the > search > > space. > > However, it does not provide information about functions used in SQL > > queries. > > > > For example, > > > >> SELECT MAX(key) from Table tab; > > > > The virtual table has no way to know the function MAX is called > > SQLite has a special optimization for this particular query, and > rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1". > Yes, I see. I am just using MAX for an example. > > The virtual table provides xFindFunction to override functions (maybe > this > > can be used to pass some information). > > For example, the full-text search module overrides the match() function; > you could do something similar: > SELECT key FROM tab WHERE tab MATCH 'max(key)' > Thanks, I think MATCH will be one workaround. > > > But it seems only general functions can be override -- it has not effect > on > > aggregate functions. > > The virtual table interface does not allow access to all the internals > of the query optimizer. > > When there is an aggregate function, you can filter the rows that will > be given to it, but the actual aggregation is still done by SQLite. > Can you tell more on this? According to my understanding, the constraints appeared in WHERE clause will be passed to virtual table (through xBestIndex() ), then xFilter() can use the information to narrow the search space. However, as for functions, virtual table does not know which function is called, so the filter is not able to filter rows based on the function. Let me know if I am not correct. > > If you can compute aggregates more efficiently than SQLite, you could > create a separate virtual table: > SELECT max_key FROM tab_agg > but this would not work for more complex queries. > I think using separate tables might not be a good solution if the table keeps updating -- there might be consistency issues. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
