CODEWITHSUNDEEP
sqloracle-database
tputkonen
tputkonen

Reputation: 5729

How to speed up SQL query with group by statement + max function?

I have a table with millions of rows, and I need to do LOTS of queries which look something like:

select max(date_field) 
where varchar_field1 = 'something' 
group by varchar_field2;

My questions are:

Upvotes: 3

Views: 12828

Answers (3)

O. Jones
O. Jones

Reputation: 108651

You can create yourself an extra table with the columns

  varchar_field1  (unique index)
  max_date_field

You can set up triggers on inserts, updates, and deletes on the table you're searching that will maintain this little table -- whenever a row is added or changed, set a row in this table.

We've had good success with performance improvement using this refactoring technique. In our case it was made simpler because we never delete rows from the table until they're so old that nobody ever looks up the max field. This is an especially helpful technique if you can add max_date_field to some other table rather than create a new one.

Upvotes: -1

Andomar
Andomar

Reputation: 238078

An index on (varchar_field1, varchar_field2, date_field) would be of most use. The database can use the first index field for the where clause, the second for the group by, and the third to calculate the maximum date. It can complete the entire query just using that index, without looking up rows in the table.

Upvotes: 17

Dmitry
Dmitry

Reputation: 3780

Obviously, an index on varchar_field1 will help a lot.

Upvotes: 0

Related Questions