CODEWITHSUNDEEP
mysqlperformanceaggregate-functionsgreatest-n-per-group
Blaskovicz
Blaskovicz

Reputation: 6160

Need Help Speeding up an Aggregate SQLite Query

I have a table defined like the following...

CREATE table actions (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  end BOOLEAN,
  type VARCHAR(15) NOT NULL,
  subtype_a VARCHAR(15),
  subtype_b VARCHAR(15),
);

I'm trying to query for the last end action of some type to happen on each unique (subtype_a, subtype_b) pair, similar to a group by (except SQLite doesn't say what row is guaranteed to be returned by a group by).

On an SQLite database of about 1MB, the query I have now can take upwards of two seconds, but I need to speed it up to take under a second (since this will be called frequently).

example query:

SELECT * FROM actions a_out 
WHERE id = 
  (SELECT MAX(a_in.id) FROM actions a_in 
   WHERE a_out.subtype_a = a_in.subtype_a 
     AND a_out.subtype_b = a_in.subtype_b 
     AND a_in.status IS NOT NULL 
     AND a_in.type = "some_type");

If it helps, I know all the unique possibilities for a (subtype_a,subtype_b)

eg:

(a,1)
(a,2)
(b,3)
(b,4)
(b,5)
(b,6)

Upvotes: 1

Views: 517

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562330

This is the greatest-in-per-group problem that comes up frequently on StackOverflow.

Here's how I solve it:

SELECT a_out.* FROM actions a_out
LEFT OUTER JOIN actions a_in ON a_out.subtype_a = a_in.subtype_a 
    AND a_out.subtype_b = a_in.subtype_b
    AND a_out.id < a_in.id
WHERE a_out.type = "some type" AND a_in.id IS NULL

If you have an index on (type, subtype_a, subtype_b, id) this should run very fast.

See also my answers to similar SQL questions:

Or this brilliant article by Jan Kneschke: Groupwise Max.

Upvotes: 0

CL.
CL.

Reputation: 180060

Beginning with version 3.7.11, SQLite guarantees which record is returned in a group:

Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value.

So can be implemented in a much simpler way:

SELECT *, max(id)
FROM actions
WHERE type = 'some_type'
GROUP BY subtype_a, subtype_b

Upvotes: 1

palako
palako

Reputation: 3470

Is this any faster?

select * from actions where id in (select  max(id) from actions where type="some_type" group by subtype_a, subtype_b);

Upvotes: 0

Related Questions