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.

10 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.