Page 1 of 1

improving search time of a query

Posted: Thu Apr 07, 2005 1:32 am
by pelegk2
i have this query :

Code: Select all

select distinct order_header.order_id
 from order_header
 LEFT JOIN order_rows on order_header.order_id=order_rows.order_id
  where
( (TAARICH_HASPAKA >= 1112853600 AND TAARICH_HASPAKA <= 1113026399 AND (sent='0' OR sent='')) OR (TAARICH_HASPAKA < 1112853600 AND TEUDAT_MISH='' AND (sent='0' OR sent='') ) OR (TAARICH_TEUDA >= 1112853600 AND TAARICH_TEUDA <= 1112939999))
and region_id='002'


when i run this query withought the left join i get results in seconds.
when i add the left join it takes 3 times more!
when i remove the
and region_id='002'

(which means go over all the regions it takes me 2-3 minutes at least!!!!)
currently i have in the order_header 50000 lines and in the order_rows i have 200000 lines!
i added some indexes but no luck
still everything is very very slow
what can i do?
thnaks in advance
peleg

Posted: Thu Apr 07, 2005 1:49 am
by feyd

Posted: Fri Apr 08, 2005 12:50 pm
by JAM
Worth mentioning might be [not] IN

Code: Select all

... in('0','1','') ...

and [not] between <min> and <max>

Code: Select all

... TAARICH_TEUDA between 1112853600 AND 1112939999 ...
Not sure how much this will effect the speed tho.