CODEWITHSUNDEEP
sqlsqlite
LodeRunner
LodeRunner

Reputation: 8423

Get maximum value over multiple tables

In my SQLite database, in each table, there is a sync_id column. I regularly want to retrieve the maximum sync_id for each table. Here's what I tried first:

SELECT
    MAX(answer.sync_id),
    MAX(community.sync_id),
    MAX(question.sync_id),
    MAX(topic.sync_id)
FROM
    answer,
    community,
    question,
    topic;

This query took forever, I actually never got to the end of it.

Here's what I tried next:

SELECT "answer" AS name, MAX(answer.sync_id) AS max_sync_id FROM answer
    UNION SELECT "community" AS name, MAX(community.sync_id) AS max_sync_id FROM community
    UNION SELECT "question" AS name, MAX(question.sync_id) AS max_sync_id FROM question
    UNION SELECT "topic" AS name, MAX(topic.sync_id) AS max_sync_id FROM topic;

This one is blazingly fast and gives me the results I expected.

I have 2 questions about this:

Upvotes: 1

Views: 73

Answers (1)

chilladx
chilladx

Reputation: 2227

1/ Why are the queries so different

Because the first one makes a big table as the cartesian product of the 4 tables before running the select against it, while the second one fires 1 request per table before aggregating the results in 4 lines. The execution plan of both requests can show that in details.

2/ Is there a way to get the result set of the 1st query with the speed of the 2nd query?

No. This is because of the nature of your data: seems like your 4 tables are not related anyhow, so you can't have a single (fast) request to hit them all. The best would probably be to make 4 requests, and group your results in your application.

Upvotes: 1

Related Questions