Re: Optimize SELECE SQL Query

On 24 Apr., 14:39, beerora <beersa.bee..._at_gmail.com> wrote:
> Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
> where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
> a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
>
> How can I optimize this for better result?

IMHO it looks like you'd better optimize your data model, not the query, so you would not have to cross-join table02 to table01. However, maybe you can optimize the query by using exists:

Select a.id,a.name,a.age,a.class,a.tel from table01 a where a.name=USER and exists
(select * from table02 where a.id in=b.id01 or a.id=b.id02 or a.id=b.id03 or
a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)

Or as table01 seems to be very big and table02 assumingly rather small, build a set of ids you are looking for first:

Select a.id,a.name,a.age,a.class,a.tel from table01 a where a.name=USER and a.id in
(select id01 from table02
 union
 select id02 from table02
 union
 select id03 from table02
 union
 select id04 from table02
 union
 select id05from table02
 union
 select id06 from table02
) Received on Wed Apr 25 2007 - 08:56:46 CDT