help debugging a mysql query

sfullman

Hi There,

I'm working on what is for me a very complex project, developing a fully relational accounting database which can accommodate true double-entry bookkeeping. The architecture of that I won't address here, but the queries are fairly slow with about 45000 records in the primary finan_transactions table. Here is the query:

EXPLAIN SELECT h.ID, h.HeaderType, h.HeaderDate, h.HeaderNumber, h.ResourceType, h.ResourceToken, h.SessionKey, h.Clients_ID, h.Contacts_ID, h.Accounts_ID AS HeaderAccounts_ID, h.Classes_ID AS HeaderClasses_ID, h.Notes, h.CreateDate, h.Creator, h.EditDate, h.Editor, COUNT( DISTINCT t.ID ) AS LineItemCount, t.ID AS Transactions_ID, SUM( t.Extension ) AS OriginalTotal, SUM( t.Distributions ) AS Distributions/* for a payment Distributions < OriginalTotal means the payment has a fully or partially unapplied amount*/, t.ParentTransactions_ID, SUM( t.AmountApplied ) AS AmountApplied, SUM( t.DistributedTo ) AS DistributedTo, SUM( t.AmountAppliedTo ) AS AmountAppliedTo, t.ChildTransactions_ID
FROM finan_headers h, _v_finan_transactions_distributions t
WHERE h.ID = t.Headers_ID
AND h.Accounts_ID != t.Accounts_ID
GROUP BY h.ID

and here is the result:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	45600 	Using temporary; Using filesort
1 	PRIMARY 	h 	eq_ref 	PRIMARY 	PRIMARY 	4 	t.Headers_ID 	1 	Using where
2 	DERIVED 	t 	ALL 	NULL 	NULL 	NULL 	NULL 	45600 	Using filesort
2 	DERIVED 	tt 	ref 	ChildTransactions_ID 	ChildTransactions_ID 	4 	cpm151_sanmarcos.t.ID 	1 	 
2 	DERIVED 	tt2 	ref 	ParentTransactions_ID 	ParentTransactions_ID 	4 	cpm151_sanmarcos.t.ID 	1

I have not used the EXPLAIN command that much - could you help me identify bottlenecks and prioritize this? I really appreciate your assistance and those who contribute on this forum! Thanks

dagon

are h.ID, t.Headers_ID, h.Accounts_ID , t.Accounts_ID all indexed ?

sfullman

yes they are indexed; i am pretty good about indexing keys that will be referenced in joins

table _v_finan_transactions_distributions is itself a view (my naming convention); here is the structure of that view

SELECT
t.*,
COUNT(DISTINCT tt.ParentTransactions_ID) AS Distributions,
SUM(IF(tt.AmountApplied,tt.AmountApplied,0)) AS AmountApplied,
tt.ParentTransactions_ID,
COUNT(DISTINCT tt2.ChildTransactions_ID) AS DistributedTo,
SUM(IF(tt2.AmountApplied,tt2.AmountApplied,0)) AS AmountAppliedTo,
tt2.ChildTransactions_ID /* this is the transaction to which THIS transaction distributed an amount, e.g. if it was a payment */
FROM
finan_transactions t LEFT JOIN finan_TransactionsTransactions tt ON t.ID=tt.ChildTransactions_ID
LEFT JOIN finan_TransactionsTransactions tt2 ON t.ID=tt2.ParentTransactions_ID
GROUP BY t.ID;

and the result:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	t 	ALL 	NULL 	NULL 	NULL 	NULL 	45600 	Using filesort
1 	SIMPLE 	tt 	ref 	ChildTransactions_ID 	ChildTransactions_ID 	4 	cpm151_sanmarcos.t.ID 	1 	 
1 	SIMPLE 	tt2 	ref 	ParentTransactions_ID 	ParentTransactions_ID 	4 	cpm151_sanmarcos.t.ID 	1

Be aware that the finan_TransactionsTransactions table originally had ChildTransactions_ID and ParentTransactions_ID together as the primary key - and the query would just hang and mysql would peg at 100% CPU. I then removed the primary key, and just did a simple index on both fields, and now

select * FROM _v_finan_transactions_distributions

will run in about 1.5 seconds. With only 46000 records in finan_transactions, and 10000 records in finan_TransactionsTransactions, that seems very slow.