At 10:50 AM 4/29/01 -0000, yeohray@... wrote:

>A query :)
>
>Table information: cti_
>Size : ~ 6MB
>Total rows : ~47000
>Rows meeting sub query : 1500
>Indexes : composite index on (fcode, trxmdate)
> single index on (trxmdate)
>
>
>-- direct query
>SELECT desc1, SUM(qty) FROM cti_
>WHERE trxmdate in (
> SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')
> )
>GROUP BY desc1
>ORDER BY 2
>
>= 38 seconds
>
>-- indirect query
>CREATE TABLE summary1 (trxmdate VARCHAR(20))
>
>INSERT INTO summary1
> SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')
>
>SELECT b.desc1, SUM(b.qty) FROM summary1 a
>INNER JOIN cti_ b
>GROUP BY b.desc1
>ORDER BY 2
>
>= 2 seconds
>
>I believe that I am doing something wrong in the direct query (the second

query would take too much work!). Could someone pls explain why the second
method is so much faster?

>

Easy. Busted optimizer. It's been broken since V4.

Jim Starkey