We need to perform lot of different adhoc queries to different databases,
and these queries might includes cross-database joins. By different types
of databases I mean, e.g., sqlite vs mysql, not just two sqlite database
files. And these queries should be executed adhoc automatically. Kind of
small cross-database engine.
To be more specific query might be like this:
SELECT * FROM (
SELECT * FROM <sqlite>.X WHERE ...
) as X
CROSS JOIN <mysql>.Y;
The application need to choose of few possible options:
1. Upload <mysql>.Y to temporary table in database <sqlite> and perform
join in <sqlite>.
2. Upload results of subquery to <mysql> and perform join here.
Decision is made by estimating cost of each option which include a lot of
terms, but the key component is the estimated number of rows. We cannot
count() rows in each subquery because it might take a lot of time.
We know that it migh look like a strange/bad system design. But,
unfortunatelly, it is a requirement.
Probably we'll choose to use sqlite stat tables to made some simplified
estimations by ourselves - it is better than nothing.
Anyway, thank you for your reply!
2016-01-14 17:00 GMT+03:00 Richard Hipp <drh at sqlite.org>:
> On 1/14/16, ???????? ??????? <belikov.vl at gmail.com> wrote:
> > Hello!
> >
> > Is there any way to get estimate number of rows that will be returned by
> > query (i.e. predicted by optimizer) without actual execution of this
> query?
> >
> > In older version it was printed in the output of EXPLAIN statement, but
> it
> > looks like it was ommited in v3.8.
> >
> > Also it looks like API function sqlite3_stmt_scanstatus() might do the
> job,
> > but I didn't find any examples of usage, while it requires some good
> > knowledge of sqlite core. Anyway it is not a preferable because in most
> > cases this function does not included in release versions of library.
> >
> > Is there any other ways to grab predicted number of rows?
> >
>
> Nope. sqlite3_stmt_scanstatus(pStmt, SQLITE_SCANSTAT_EST, ...) is
> your only option.
>
> Why do you want this information?
> --
> D. Richard Hipp
> drh at sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Vladimir Belikov
