Mysql – the best way to find out the sum of a field from a table

database-designMySQLsum

I am making a accounting software. I have a table debit_credit that contains double entry accounting records

Here is my example

data.Table is:

debit_credit_id | tran_id | ledger_head_id | debit | credit | dr_cr | commission | commission_type
--------------------------------------------------------------------------------------------------
1               | 2       |  5             | 500   | 0      | Dr    | null           | null

1               | 2       |  5             | 0     | 500    | Cr    | null           | null

This tables contains many rows and I have to run the following query several times:

'SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id=$id'

But my project manager proposed, to make a default view to making the sum. After that, when I need the sum just query from that view as

'SELECT debit - credit FROM debit_credit_view WHERE ledger_head_id=$id';

But I would prefer to use first method with stored procedure instead of the method proposed by my project manager.

Sql fiddle: http://sqlfiddle.com/#!9/cc594/1

What is the best way?

Best Answer

http://sqlfiddle.com/#!9/cc594/6 - you can use the "View Execution Plan" link to show explain for those queries - from that you will see that selecting only specific ledger_head_id from the view (query #4) evaluates the view on entire table and only then filters the wanted rows - that is BAD as for info about one id you read entire table of potentially millions of rows.

Always use the full query (#2 or similar depending how many ids you want at the moment), make a function with a bit of code to create it if needed but do not put that inside a view.

Add indexes - you probably have the primary key on debit_credit_id and you should have index on ledger_head_id at least, maybe even a multicolumn one on (ledger_head_id, debit, credit) to cover that query if performance is critical enough. (That will make the difference between full query and a view even bigger - http://sqlfiddle.com/#!9/fe306/1 )

One small note: In your view you select the dr_cr column from grouping - that might return random data into that column as the "group" has many rows and they can have different values in that column. It seems from your test data that Dr/Cr distinction is functionally dependent on ledger_head_id but that might be a coincidence, so recheck it.

Related Question