Re: Subset Query Speed Help from .46 second to 7 minutes!
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