Re: count and count distinct

I've table with about 600 000 records. I see that 'count' and 'count distinct' are running about 4-6 minutes. What should I do (on which column put indexes, or substitute these operations by others) to improve performance? Thanks, Anna Zygmunt

Anna, As far as count goes, I think any column(s) would be suitable for count(*) or the specific column for count(columnname), depending on the design, assuming no grouping or select conditions (both of which could dictate index selection). For count distinct, you want an index that is headed by the column being counted, unless you are getting a count distinct with group by, in which case the group by columns should be first. If however, you are selecting by other columns as well as grouping, then you may want an index by the selecting columns, since the optimizer may prefer to use the selecting index and then sort for the grouping. However,..... The short answer is, "it depends". But given 600000 rows, I have to wonder if the engine is well-tuned. How large are the rows, how many pages does the table occupy, how does this compare to BUFFERS, can BUFFERS be increased, do you have sufficient temporary area for sorting, how many cpuVPs are you running, have you updated statistics on the table per standard recommendations, have you 'explained' the selects to see how they are being processed..... these are all questions that need to be addressed BEFORE you start adding indexes willy-nilly. Hope that helps, Doug "Anna Zygmunt" <azygmunt@agh.edu.pl> wrote in message news:8qad29$qbd$1@galaxy.uci.agh.edu.pl... > I've table with about 600 000 records. > I see that 'count' and 'count distinct' > are running about 4-6 minutes. > What should I do (on which column put indexes, > or substitute these operations by others) > to improve performance? > > Thanks, > Anna Zygmunt > >