MySQL Forums
Forum List  »  Performance

Re: Subset Query Speed Help from .46 second to 7 minutes!
Posted by: Brian Pilati
Date: August 15, 2005 11:21AM

Jay Pipes wrote:
>
> Below, I take the above query and, based on
> your schema, put in a join condition
> relating the location and location_connection
> tables the way I *think* it should
> be done (just guessing though).
>
> The below query should run in less than a
> second with the proper indexes on the tables.
> */
>
> SELECT DISTINCT
> i.id
> , gl.id
> , i.reference_number
> , IF( gl.credit > 0 , gl.credit , - gl.debit )
> AS amount
> , COALESCE(invoiced_transfers.debit,
> prepayments.debit) as payment
> , ROUND((UNIX_TIMESTAMP( '2005-08-01 23:59:59' ) -
> UNIX_TIMESTAMP( i.date_posted ))/ 86400) AS
> elapsed
> , gl.date
> FROM location l
> INNER JOIN location_connection lc
> ON l.id = lc.location_id
> INNER JOIN invoices i
> ON lc.invoice_id = i.id
> INNER JOIN general_ledger gl
> ON i.id = gl.parent_id
> LEFT JOIN company c
> ON l.company_id = c.id
> LEFT JOIN countries co
> ON l.country = co.id
> LEFT JOIN
> (
> SELECT
> gl2.general_ledger_id, gl2.debit
> FROM general_ledger gl2
> INNER JOIN invoices i2
> ON gl2.parent_id = i2.id
> WHERE i2.posted = 'true'
> AND gl2.type = 'Invoiced Transfer'
> AND i2.date_posted <= '2003-01-31 23:59:59'
> ) AS invoiced_transfers
> ON gl.id = invoiced_transfers.general_ledger_id
> LEFT JOIN
> (
> SELECT
> gl2.general_ledger_id, gl2.debit
> FROM general_ledger gl2
> INNER JOIN invoices i2
> ON gl2.parent_id = i2.id
> WHERE i2.posted = 'false'
> AND gl2.type = 'Prepayment'
> AND i2.date_posted <= '2003-01-31 23:59:59'
> ) AS prepayments
> ON i.id = prepayments.general_ledger_id
> WHERE l.accounting_type = 'Vendor'
> AND lc.location_id = '37'
> AND i.posted = 'true'
> AND gl.posted = 'true'
> AND gl.type IN ('Invoice Net','Prepayment')
> AND lc.type IN ('Invoice To','Bank Transfer')
> AND gl.gl = '5'
> AND i.date_posted <= '2003-01-31 23:59:59'
> ORDER BY
> c.name
> , l.city
> , gl.date limit 11;
>

Jay,

WOW! I am impressed with what you have been able to do here. With your changes the query is now averages under a second. As you predicted. The other good news, a query for the last two years and a half of invoices and payment runs at 6 seconds. That rocks. I will make sure I send you my bonus! ;)

I agree with what you have said about ANSI. Works a lot better to debug.

Also you are correct about the Location_connection and location relations.

To finish off the query I have added the following: having amount - payment != 0. The having statements gives me the invoices that have balances.

Just for your knowledge I am posting the explain statements, as follows:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived3>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: gl
type: ref
possible_keys: index1,index2
key: index2
key_len: 5
ref: const
rows: 1383
Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: i
type: eq_ref
possible_keys: PRIMARY,index2
key: PRIMARY
key_len: 4
ref: dts.gl.parent_id
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: lc
type: ref
possible_keys: index1
key: index1
key_len: 10
ref: dts.i.id,const
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: l
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.lc.location_id
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.company_id
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: co
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.country
rows: 1
Extra: Using index
*************************** 8. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 168
Extra:
*************************** 9. row ***************************
id: 3
select_type: DERIVED
table: i2
type: range
possible_keys: PRIMARY,index2
key: index2
key_len: 9
ref: NULL
rows: 360
Extra: Using where
*************************** 10. row ***************************
id: 3
select_type: DERIVED
table: gl2
type: ref
possible_keys: index1
key: index1
key_len: 5
ref: dts.i2.id
rows: 5
Extra: Using where
*************************** 11. row ***************************
id: 2
select_type: DERIVED
table: i2
type: range
possible_keys: PRIMARY,index2
key: index2
key_len: 9
ref: NULL
rows: 360
Extra: Using where
*************************** 12. row ***************************
id: 2
select_type: DERIVED
table: gl2
type: ref
possible_keys: index1
key: index1
key_len: 5
ref: dts.i2.id
rows: 5
Extra: Using where
12 rows in set (0.10 sec)

If you see anything else I should do, please let me know.

BTW- I tried to pull-up Amazon.com to purchase your book and Amazon seems to not be working. I have a great interest now in your work. It seems you have been able to take queries to another level.

Thanks again, for your help.
Brian

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.