SQLite Forum

There is not much difference between the two queries when I run them with such a small amount of data, though the merge then lookup is somewhat faster than the lookup then merge.  This is probably entirely due to the fact that doing a "union" (B-Tree sort/merge) is faster when working with simple integers than it is with longer text strings.  

Interestingly, even if the output is sorted by name, the first query is faster than the second when passing the results back to an application than the second.

(Are you including the time taken to produce the output on the screen rather than the time to run the query?  It takes about 19 seconds or so more to "output the data to the screen" on my computer, or an additional .4 seconds or so to pass the records back to an application (python) when not output to the screen.)

```
sqlite> .timer on
sqlite> .eqp on
sqlite> select count(name)
   ...>   from (
   ...>         select nameid
   ...>           from allnouns
   ...>         union
   ...>         select nameid
   ...>          from allverbs
   ...>        ) as T
   ...>   join names
   ...>     on names.nameid = T.nameid
   ...> ;
QUERY PLAN
|--MATERIALIZE 2
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SCAN TABLE allnouns (~1048576 rows)
|     `--UNION USING TEMP B-TREE
|        `--SCAN TABLE allverbs (~1048576 rows)
|--SCAN SUBQUERY 2 AS T (~2097152 rows)
`--SEARCH TABLE names USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
94813
Run Time: real 0.165 user 0.156250 sys 0.000000
sqlite>
sqlite> select count(name)
   ...>   from (
   ...>         select name
   ...>           from names
   ...>          where nameid in (
   ...>                           select distinct nameid
   ...>                             from allnouns
   ...>                          )
   ...>        union
   ...>         select name
   ...>           from names
   ...>          where nameid in (
   ...>                           select distinct nameid
   ...>                             from allverbs
   ...>                          )
   ...>        )
   ...> ;
QUERY PLAN
|--CO-ROUTINE 4
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SEARCH TABLE names USING INTEGER PRIMARY KEY (rowid=?) (~24 rows)
|     |  `--LIST SUBQUERY 1
|     |     `--SCAN TABLE allnouns USING COVERING INDEX an_nid (~1048576 rows)
|     `--UNION USING TEMP B-TREE
|        |--SEARCH TABLE names USING INTEGER PRIMARY KEY (rowid=?) (~24 rows)
|        `--LIST SUBQUERY 3
|           `--SCAN TABLE allverbs USING COVERING INDEX av_nid (~1048576 rows)
`--SCAN SUBQUERY 4 (~48 rows)
94813
Run Time: real 0.175 user 0.171875 sys 0.000000
```