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?

  • 2
    Why don't you run an EXPLAIN to see which is more efficient? I'm guessing, but I reckon the second one will work better. BTW, you should format your SQL better - makes it far more readable - I edited it to put spaces between the minus symbols and the operands. Welcome to the forum :-) – Vérace Aug 5 '15 at 5:33
  • You probably want to add GROUP BY ledger_head_id to the query and ledger_head_id to selected columns - and then remove the WHERE clause - that way you can get sums for all ids at once (or do WHERE ledger_head_id IN (<list of ids>) to get only some but in one query. – jkavalik Aug 5 '15 at 5:51
  • @jkavalik, I could not realize your comment fully. Could you provide me the query ? Can i use join with group_ledger on creating view ? – Kabir Hossain Aug 5 '15 at 6:07
  • @KabirHossain Read something about what GROUP BY does. And try not to use a view for it if possible, mysql views may be quite hard to optimize. You can experiment and share some examples with us using sqlfiddle.com - if you prepare some testing scheme and data and add it to your question, we can show you exact queries with some output. – jkavalik Aug 5 '15 at 6:41
  • @jkavalik, sql fiddle added. – Kabir Hossain Aug 6 '15 at 5:16

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.

  • Yes, Dr/Cr distinction is functionally dependent on ledger_head_id. I am running another query for taking the group using ledger_head_id from group table. Could you suggest me to use second query with index key ? – Kabir Hossain Aug 6 '15 at 6:28
  • If it is dependent then your schema is not normalized as it should show only in one row in some table where ledger_head_id is a primary key probably. Not sure what you mean by "use second query with index key" - maybe what I used in the second fiddle ? there are two indexes added, you can check the explains. – jkavalik Aug 6 '15 at 6:34
  • @ jkavalik , You used ledger_head_id IN (2) instead of ledger_head_id=2 . Second is not better than first ? – Kabir Hossain Aug 16 '15 at 8:34
  • @KabirHossain They are the same in this case - IN is usually overwritten by optimizer to a set of ORs with equality. But I used IN to suggest you can send multiple IDs at once, because you wrote "... I have to run a query several times ..." which suggested you might be interested in getting results for multiple ledger_head_id in one run. – jkavalik Aug 16 '15 at 9:28

VIEWs do not improve performance. (You are talking about CREATE VIEW, correct?)

Either of these is perfectly good:

SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id = $id
SELECT SUM(debit - credit)      FROM debit_credit WHERE ledger_head_id = $id
  • No, the statements are different if the columns contain NULL values. – miracle173 Aug 6 '15 at 0:08
  • The table contains two null values... – Kabir Hossain Aug 6 '15 at 4:42
  • I don't think NULL impacts SUM. – Rick James Aug 6 '15 at 16:27
  • Oh, I guess it would be questionable if debit were NULL while credit were not. – Rick James Oct 10 '15 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.