SQLite Forum

big performance hit when querying 2 values in a single query instead of two
Use `EXPLAIN` or `EXPLAIN QUERY PLAN` preface to the queries to see what is happening.  In the CLI you can also use `.eqp on` which automagically does `explain query plan` or `.eqp full` to automagic `explain`.

Try using this form:

select (select max(id) from pro_comment),
       (select count(distinct id) from pro_comment)

This allows each scalar subquery to be optimized (executed) independently.  Using both max(id) and count(distinct id) in the same single query prevents the optimizer from applying some optimizations (particulary the minmax optimization).

However, even as expressed, the total time taken for the combined query will be the same.  Your last select statement `select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;` should not take more time than a mere `select count(distinct id) from pro_comment;` since that requires a full scan of the table -- I cannot explain what is happening on the computer that is using the additional 47 seconds of CPU or 2 seconds of system time other than to observe that some OTHER process is going full-ninja-kaboom using your CPU and I/O.