improving search time of a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

improving search time of a query

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply