Yesterday I was working on one huge db query, that took 30-50 seconds to complete on Oracle DB. I spent few hours on optimizing this query and setting indexes and as a result now it takes 4-5 seconds. Take a look Posting it just for fun.
Code:
	$sql="select".
	" tbl1.userid, tbl1.groupid, tbl1.name,".
	" tbl2.used as fastUsed1, tbl10.used as iptUsed1, tbl3.paid as paid1,".
	" tbl4.used as fastUsed2, tbl11.used as iptUsed2, tbl5.paid as paid2,".
	" tbl6.used as used3, tbl7.paid as paid3,".
	" tbl8.used as used4, tbl9.paid as paid4".
		" from".
			//LIST OF USERS
			" (select".
				" uao.userid,".
				" uao.groupid,".
				" (case when u.is_juridical=1 then u.organization_name else u.userSurname ||  ' ' || u.userName end) as name".
			" from".
				" user_account_info uao inner join users u on u.userid=uao.userid group by uao.userid, uao.groupid, (case when u.is_juridical=1 then u.organization_name else u.userSurname ||  ' ' || u.userName end) order by name) tbl1".
			" left join".
			//INITIAL FAST INTERNET USED
			" (select".
				" userid,".
				" SUM(cost) as used".
			" from".
				" flows_aggregated where flowdate < to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; group by userid&#41; tbl2 on tbl1.userid=tbl2.userid".
			" left join".
			//INITIAL PAYMENTS
			" &#40;select".
				" userid,".
				" SUM&#40;sum&#41; as paid".
			" from".
				" payments where paymentdate < to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and paymentfor in &#40;$services&#41; group by userid&#41; tbl3 on tbl1.userid=tbl3.userid".
			" left join".
			//REMAINDER FAST INTERNET USED
			" &#40;select".
				" userid,".
				" SUM&#40;NVL&#40;COST,0&#41;&#41; as used".
			" from flows_aggregated where flowdate between to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; AND to_date&#40;'$dateTo', 'YYYY-MM-DD HH24&#58;MI'&#41; group by userid&#41; tbl4 on tbl1.userid=tbl4.userid".
			" left join".
			//REMAINDER PAYMENTS
			" &#40;select".
				" userid,".
				" sum&#40;NVL&#40;sum,0&#41;&#41; as paid".
			" from payments where paymentdate between to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and to_date&#40;'$dateTo', 'YYYY-MM-DD HH24&#58;MI'&#41; and paymentfor in &#40;$services&#41; group by userid&#41; tbl5 on tbl1.userid=tbl5.userid".
			" left join".
			//INITIAL STORNO USED
			" &#40;select".
				" userid,".
				" SUM&#40;sum&#41; as used".
			" from stornoes where stornotype=2 and stornodate < to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and servicetype in &#40;$services&#41; group by userid&#41; tbl6 on tbl1.userid=tbl6.userid".
			" left join".
			//INITIAL STORNO PAID
			" &#40;select".
				" userid,".
				" SUM&#40;sum&#41; as paid".
			" from stornoes where stornotype=1 and stornodate < to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and servicetype in &#40;$services&#41; group by userid&#41; tbl7 on tbl1.userid=tbl7.userid".
			" left join".
			//REMAINDER STORNO USED
			" &#40;select".
				" userid,".
				" SUM&#40;sum&#41; as used".
			" from stornoes where stornotype=2 and stornodate between to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and to_date&#40;'$dateTo', 'YYYY-MM-DD HH24&#58;MI'&#41; and servicetype in &#40;$services&#41; group by userid&#41; tbl8 on tbl1.userid=tbl8.userid".
			" left join".
			//REMAINDER STORNO PAID
			" &#40;select".
				" userid,".
				" SUM&#40;sum&#41; as paid".
			" from stornoes where stornotype=1 and stornodate between to_date&#40;'$dateFrom', 'YYYY-MM-DD HH24&#58;MI'&#41; and to_date&#40;'$dateTo', 'YYYY-MM-DD HH24&#58;MI'&#41; and servicetype in &#40;$services&#41; group by userid&#41; tbl9 on tbl1.userid=tbl9.userid".
			" left join".
			//INITIAL IPT USED
			" &#40;select".
				" userid,".
				" SUM&#40;cost&#41; as used".
			" from voip_calls where datetimedisconnect < '".strtotime&#40;$dateFrom&#41;."' group by userid&#41; tbl10 on tbl1.userid=tbl10.userid".
			" left join".
			//REMAINDER IPT USED
			" &#40;select".
				" userid,".
				" SUM&#40;cost&#41; as used".
			" from voip_calls where datetimedisconnect between '".strtotime&#40;$dateFrom&#41;."' and '".strtotime&#40;$dateTo&#41;."' group by userid&#41; tbl11 on tbl1.userid=tbl11.userid &#123;whereClause&#125;";
This is the hugest query I ever wrote, and I can't think of further optimization. If someone else will have recommendations on how to make it faster, I'll be surprised.