Let’s take a closer look at the
xFilter() functions. Both of our example modules were
fairly simple and didn’t use them, but proper use of these functions is
critical for internal modules that implement some types of high-performance
By default, the only way to get data out of a table—virtual or otherwise—is to do a full table scan. This can be quite expensive, especially if the table is large and the query is trying to extract a small number of rows.
Standard tables have ways of boosting retrieval speeds, such as using indexes. The query optimizer can use other hints found in a standard table definition, such as knowing which columns are unique or have other constraints on them.
Virtual tables lack these features. You cannot
create an index on a virtual table, and the query optimizer has no
knowledge of the structure or format of a virtual table, other than
the column names. The only known constraint on a virtual table is that
each virtual row must have a unique, integer
Without any additional information, it is very difficult to optimize a query that involves a virtual table. This is true for both the query planner and the virtual table itself. For the best performance, the query optimizer needs to understand what types of lookups the virtual table is best suited to doing. Conversely, the virtual table module needs to understand the nature of the user query, including any constraints, so that it can use ...