CODEWITHSUNDEEP
sqlsqliteunion-all
James Jones
James Jones

Reputation: 1506

sqlite union all optimization

I have a sqlite DB query that looks something like this:

SELECT
    origin,
    destination,
    weight,
    rate,
    0 as group
    from groupAZones, groupARates
    where
    tms = groupZone

union all

SELECT
    origin,
    destination,
    weight,
    rate,
    1 as group
    from groupBZones, groupBRates
    where
    tms = groupZone

    union all

SELECT
    origin,
    destination,
    weight,
    rate,
    2 as group
    from groupCZones, groupCRates
    where
    tms = groupZone

    union all

SELECT
    origin,
    destination,
    weight,
    rate,
    3 as group
    from groupDZones, groupDRates
    where
    tms = groupZone

Is there a good way to optimize a query like this? I'm trying to create a simple view that combines these 4 tables. With this used as the view query, queries into the view are taking about 13 secs.

I tried creating indexes for the 4 tables but it didn't seem to help.

I'm kind of a novice when it comes to SQL, I know enough to do the simple things but I'm still learning the advanced tricks.

Any pointers or info would be helpful.

Upvotes: 0

Views: 667

Answers (1)

James Jones
James Jones

Reputation: 1506

CL pointed out that I should run the EXPLAIN QUERY PLAN command. This led to the discovery that two of the 4 indexes were not being used.

Thanks CL!

I'm still wondering if there is a better way to do that query, but for now it's doing what I need it to do.

Upvotes: 1

Related Questions