I have a query with multiple table joints and where clauses. Tables are huge and query runs for a very long time. I need to run another query, which is exactly the same apart for one Where clause (first time price = "P", second time price = "S"). I use this for BIRT report, which means I need to have a result of there two queries at the same time. I can run them at the same time, but because they are both huge it takes a Long time. I wonder if I can make only one query, will it be faster? And if it will- what’s the best way to make it?
Thank you for your help!
Please let me know if it is not clear.

9 Years
Discussion Span
Last Post by nav33n

Since you want to run exactly the same query and check only for one condition, you can do it this way.

$query="select * from huge_table where conditions joins etc";

So, by the end of the execution of this query, you will have 2 arrays, 1 with price=p values and the other with price=s values.



Thank you for your reply, but i wasn't clear.
Roughly my code:

Select * FROM sales_item d

join (select str_to_date('2007-09-30', '%Y-%m-%d') st_date) v

join item_store f on f.item_no=d.item_no and f.store_id = d.store_id

join item_sellprice_store a on f.item_no=a.item_no and f.store_id = a.store_id

join item b on f.item_no=b.item_no

join item_supplier c on b.item_no=c.item_no and b.supplier_no=c.supplier_no

join item_sellprice_storegroup g on a.batch_id=g.batch_id

join store k on f.store_id =k.store_id

where d.store_id = '011'

and (f.item_status ='A' or f.item_status ='O')

and a.price_type='P'

and a.effective_start_date < v.st_date < a.effective_end_date

and d.sale_date = v.st_date

order by d.category_code, a.sell_gst, a.item_no ;

And the second one is exactly the same apart from highlighted line (it▓s a.price_type='S')

I need a query, not a script.
Thank you so much.


Umm.. I didnt get you. Do you type this query in phpmyadmin/mysql prompt directly without using a php interface ? How do you generate a BIRT report ?What I said was, remove the condition and a.price_type='P' . Then compare the result with the price_type.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.