One SELECT is meant to return a number of rows, and the values on each row relate to those rows. In other words, SQL does processing based on that idea: it tries to use one operation per table to get the results you asked for. Let's look at your SELECT:
<code> select 'max id & count distinct:', max(id), count(distinct id)
from pro_comment;</code>
The <code>max(id)</code> only needs one row to be retrieved: the one which has the biggest value for <code>id</code>. Since there's an index on this value, it can do this by looking at whatever row is last in this index. Fast and easy to find.
But then in the same query you ask for <code>count(distinct id)</code>. Which requires more than one row to be read from the table. It has to scan a whole index. Well, that's okay, it has the index it needs.
But in the combined query, SQL is meant to retrieve both these figures with the same operation. That could be a lot more complicated. Let's see what it really does. Let's look at EXPLAIN QUERY PLAN on all three of those queries:
<pre>sqlite> EXPLAIN QUERY PLAN select 'max id:', max(id) from pro_comment;
QUERY PLAN
`--SEARCH pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'count distinct:', count(distinct id) from pro_comment;
QUERY PLAN
`--SCAN pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;
QUERY PLAN
|--USE TEMP B-TREE FOR count(DISTINCT)
`--SCAN pro_comment USING COVERING INDEX pro_comment_id</pre>
As you see, SQLite, in trying to get both figures from the same search, thinks it has to make a temporary index of the data. The solution is to accept that you want two different figures that aren't related, and ask for them in separate statements. Which is what you did yourself in your demonstration of the problem. Well done.
It could be that this particular optimisation is easy and fast to identify. In which case, the developers (who are reading your thread) might add it. But if you have SQLite check for every possible optimisation every time it does a search, every search is going to take a long time while SQLite checks to see if it qualifies for every possible optimisation. Which, for most SELECTs most of the time, will slow SQLite down.