SqLite query with multiple aggregation functions are slow - need help to optimize the query-CodePudding

I need help optimizing this query for sqlite (query run from php):

select max(aid) as aid, max(mid) as mid ,max(tid) as tid,
       max(m.id) as m_mid, max(t.id) as m_tid, max(a.id) as m_aid
from   dview, T_DATA t, M_DATA m, A_DATA a

Field not accessed by table name prefix (ex a.id) are from dview, other are from tables specified.

Running it in sqlite with few hundreds of records in each table takes over a minute.

I know that indexes won't fix because sqlite takes care of them only if a single aggregation is performed. What can I do?

CodePudding user response：

Your FROM clause:

from dview, T_DATA t, M_DATA m, A_DATA a

creates a cartesian product of all the combinations of rows of the 4 datasets and it produces huge dataset on which the aggregation is finally done.

As it is, your code is equivalent to:

SELECT MAX(aid) AS aid, MAX(mid) AS mid, MAX(tid) AS tid,
       (SELECT MAX(id) FROM M_DATA) AS m_mid, 
       (SELECT MAX(id) FROM T_DATA) AS m_tid, 
       (SELECT MAX(id) FROM A_DATA) AS m_aid
FROM dview;

which should perform significantly better.