0

I have a table [ID,ITEM_NAME,ITEM_PRICE,ITEM_STATUS,ITEM_TYPE,ITEM_OWNER,ITEM_DATE]

The application can query the table with any number of search conditions like with item date and/or item owner etc.

In the resultset, I also need to fetch the counts by different status in ITEM_STATUS.

Its often causing timeouts when I try to get the counts based on status.

How is this case generally handled in large volume applications. Say Y mail. I have counts of how many are in inbox, how many are read/unred/sent and what not..almost instantly. How can such an experience be achieved?

Sripaul
  • 2,227
  • 9
  • 36
  • 60
  • You might try adding indexes on the individual columns + the ID, as in (ITEM_STATUS, ID), (ITEM_NAME, ID), etc. I suggest adding the ID column to the indexes because I don't know how many distinct values may exist for a particular column, and an index on a column with few distinct values may cause DELETEs to run slowly. Share and enjoy. – Bob Jarvis - Слава Україні May 21 '13 at 11:04
  • @BobJarvis Won't it affect inserts? They would turn up too slow right – Sripaul May 21 '13 at 11:08
  • 1
    I don't believe this will cause any performance issues. I've had search tables where every column in the table (about 40 or 50 columns, if I recall correctly) each had a separate index. Performance was fine. (We built an elaborate system to update the search table in the background, then threw it out when we found it wasn't needed). In over 20 years of working with relational databases on a daily basis I can't recall a situation where indexes caused a performance problem on INSERT or any other operation, so I don't recommend worrying about this. Share and enjoy. – Bob Jarvis - Слава Україні May 21 '13 at 11:15
  • +1 Bob -- when tempted to worry about "too many indexes slowing down inserts", remember that insert happens once and queries happen maybe thousands of times. – David Aldridge May 21 '13 at 11:17

1 Answers1

0

Other than indexing, and bitmap indexes are the most flexible and performant for this kind of thing if you can deal with the concurrency issues in maintaining them, consider materialised views to cover the most common aggregation levels.

Defining multi-level materialised views can give you almost instant response times, and even allow effective indexing on HAVING clauses.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96