John Davidson

Message:

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?

---

My Answer:
One possible solution to optimize this query is to break it down into multiple smaller queries and then combine the results. This way, you can avoid the performance issue caused by multiple aggregation functions in a single query.

Here is an example of how you can rewrite the query:

php
// Query 1
$query1 = "SELECT MAX(aid) as aid, MAX(mid) as mid, MAX(tid) as tid FROM dview";
$result1 = $db->query($query1)->fetchArray(SQLITE3_ASSOC);

// Query 2
$query2 = "SELECT MAX(id) as m_mid FROM M_DATA";
$result2 = $db->query($query2)->fetchArray(SQLITE3_ASSOC);

// Query 3
$query3 = "SELECT MAX(id) as m_tid FROM T_DATA";
$result3 = $db->query($query3)->fetchArray(SQLITE3_ASSOC);

// Query 4
$query4 = "SELECT MAX(id) as m_aid FROM A_DATA";
$result4 = $db->query($query4)->fetchArray(SQLITE3_ASSOC);

// Combine the results
$finalResult = array_merge($result1, $result2, $result3, $result4);


By breaking down the query into smaller parts, you can potentially improve the performance of the overall query execution. Additionally, you can also consider optimizing the database schema and query structure to further enhance the performance.