SQL Database Design - Cache Tables?

Question

Hi there,

What's a common/best practice for database design when it comes to improving performance on count(1) queries? (Im currently using SQLite)

I've normalized my data, it exists on multiple tables, and for simple things I want to do on a single table with a good index -- queries are acceptably quick for my purposes.

eg:

SELECT count(1) from actions where type='3' and area='5' and employee='2533';

But when i start getting into multiple table queries, things get too slow (> 1 second).

SELECT count(1) from(SELECT SID from actions where type='3' and employee='2533' INTERSECT SELECT SID from transactions where currency='USD')

How should I cache my results? What is a good design? My natural reaction is to add a table solely for storing rows of cached results per employee?

Thanks!

Answer 1

If you are 100% sure that you are always repeating exactly the same query for the same customer, sure, persist the result.

However, in most other instances, RDBMS usually handles caching just fine.

The INTERSECT with the query

SELECT SID from transactions where currency='USD'

Could be problematic if there are a large number of transaction records with USD.

Possibly you could replace this with a join?

SELECT count(1) from 
(
    SELECT t.[SID] 
    from
        transactions as t
        inner join
        (
            SELECT SID from actions where type='3' and employee='2533'
        ) as a
        on t.SID = a.SID
    where t.currency= 'USD'
) as a

You might just check your indexes however:

For

• SELECT count(1) from actions where type='3' and area='5' and employee='2533'
• SELECT SID from actions where type='3' and employee='2533'

An index on Actions(Employee, Type) or Actions(Employee, Type, Area) would make sense (assuming Employee has highest selectivity, and depending on the selectivity of Type and Area).

You can also compare this to an index on Actions(Employee, Type, Area, SID) as a covering index for your second query.

And for the join above, you need an index on Transactions(SID, Currency)

HTH