0

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?

7
  • 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, 2015 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, 2015 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 ? Aug 5, 2015 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, 2015 at 6:41
  • @jkavalik, sql fiddle added. Aug 6, 2015 at 5:16

2 Answers 2

1

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.

4
  • 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 ? Aug 6, 2015 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, 2015 at 6:34
  • @ jkavalik , You used ledger_head_id IN (2) instead of ledger_head_id=2 . Second is not better than first ? Aug 16, 2015 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, 2015 at 9:28
1

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
4
  • No, the statements are different if the columns contain NULL values.
    – miracle173
    Aug 6, 2015 at 0:08
  • The table contains two null values... Aug 6, 2015 at 4:42
  • I don't think NULL impacts SUM.
    – Rick James
    Aug 6, 2015 at 16:27
  • Oh, I guess it would be questionable if debit were NULL while credit were not.
    – Rick James
    Oct 10, 2015 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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