0

Hello!
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.

2
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by nav33n
0

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";
$result=mysql_query($query);
$price_p=array();
$price_s=array();
while($row=mysql_fetch_array($result,MYSQL_ASSOC)){
   if($row['price']=="P"){
       array_push($price_p,$row['price']);
   }
   if($row['price']=="S"){
       array_push($price_s,$row['price']);
   }
}

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.

Cheers,
Naveen.

0

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.

0

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.