Re: Subset Query Speed Help from .46 second to 7 minutes!

[Brian Pilati]

Felix Geerinckx wrote:
> Brian Pilati wrote:
>
> > Hope that helps ... looks Greek to me. :)
>
> I must admit that I don't have enough time to go
> through it in detail at the moment.
> But at first sight, your query 3 might benefit
> from the following additional indexes:
>
> ALTER TABLE general_ledger ADD INDEX index3
> (general_ledger_id);
> ALTER TABLE invoices ADD INDEX index2
> (date_posted);
>

I made these changes and it cut the query time in half.

+-----+------+------------------+----------+----------+---------+---------------------+
| t1 | t2 | reference_number | amount | payment | elapsed | date |
+-----+------+------------------+----------+----------+---------+---------------------+
| 139 | 225 | 101617 | 500.00 | 500.00 | 944 | 2002-12-31 12:00:00 |
| 131 | 222 | 101698 | 14400.00 | 13900.00 | 944 | 2002-12-31 12:00:00 |
| 119 | 224 | 101694 | 16200.00 | 15700.00 | 944 | 2002-12-31 12:00:00 |
| 353 | 221 | 101671 | 14974.00 | NULL | 944 | 2002-12-31 12:00:00 |
| 147 | 223 | 101695 | 28610.00 | 28110.00 | 944 | 2002-12-31 12:00:00 |
| 25 | 162 | 5101V | 22000.00 | NULL | 935 | 2003-01-09 13:02:11 |
| 56 | 558 | 5116V | 23400.00 | NULL | 928 | 2003-01-16 15:21:16 |
| 259 | 1510 | AQ101694 | 2743.00 | NULL | 916 | 2003-01-28 11:30:35 |
+-----+------+------------------+----------+----------+---------+---------------------+
8 rows in set (3 min 48.25 sec)


BTW ... the data is correct. If I can get the query time to be negligible that will rock.

Thanks again, and keep the suggestions coming.

Brian