My query has to return a statistics for example for march containing productname
and price
and its sidedishes
(1:n relation) with the right price of each sidedish and the right tax for each sidedish and product (can be different).
SELECT count(*) as daySum,
p_name,
SUM(pp_price) as daySumPrice,
o_day,
o_time,
s_id,
s_name,
t_name,
t_id,
SUM(ust10) as ust10,
SUM(ust20) as ust20,
SUM(sdUst10) as sdUst10,
SUM(sdUst20) as sdUst20 ,
fk_p_id,
u_name
FROM
(
SELECT tbl_orders.o_id,
tbl_orders.o_comment,
DATE_FORMAT( $dateField, '$sqlDateFormat' ) AS o_day,
tbl_orders.o_time,
tbl_orders.o_t_name AS t_name,
tbl_orders.fk_t_id AS t_id,
tbl_orders.fk_u_id AS u_id,
tbl_orders.fk_s_id AS s_id,
tbl_orders.o_s_name AS s_name,
tbl_orders.o_u_name AS u_name,
tbl_orders.fk_p_id,
CONCAT(tbl_orders.o_p_name,IF(sideDish.p_name IS NULL,'',' ('),IFNULL(GROUP_CONCAT(DISTINCT(sideDish.p_name) SEPARATOR ', '),''),IF(sideDish.p_name IS NULL,'',')'),IF(tbl_orders.o_comment='','',' - ".getLanguageKey('label_comment').": '),tbl_orders.o_comment,IF(tbl_orders.o_comment='','','')) as p_name,
ROUND(IFNULL(tbl_orders.o_p_price,0)+IFNULL(sideDishPrice.pp_price,0),2) as pp_price,
IF(tbl_orders.o_p_ust=10,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust10,
IF(tbl_orders.o_p_ust=20,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust20,
sideDishPrice.ust10 AS sdUst10,
sideDishPrice.ust20 AS sdUst20
FROM tbl_orders
INNER JOIN tbl_dailyReport dr1
ON dr1.dr_id = tbl_orders.fk_dr_id
LEFT JOIN
(
SELECT o2.o_id,
SUM(IFNULL(o2.o_p_price,0)) as pp_price,o2.o_p_name as p_name,o2.o_parent AS o_parent,
SUM(IF(o2.o_p_ust=10,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust10,
SUM(IF(o2.o_p_ust=20,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust20
FROM tbl_orders o2
GROUP BY o_parent
) sideDishPrice
ON tbl_orders.o_id = sideDishPrice.o_parent
LEFT JOIN
(
SELECT o_id,o_p_name AS p_name,o_parent
FROM tbl_orders
GROUP BY o_id
) sideDish
ON tbl_orders.o_id = sideDish.o_parent
WHERE
tbl_orders.fk_c_id = '$c_id'
$stornoNull
AND tbl_orders.o_parent IS NULL
$onlyDayClosingData
$whereClauseTime
$whereClauseID
$whereClauseStorno
GROUP BY tbl_orders.o_id,tbl_orders.o_p_name,sideDish.o_parent
) products $groupBy $orderBy;
The query is working and it gets the right numbers but it takes too long.
rows in database: 7000 orders in march: 3500 time to print 6 of these queries with different group by's (date, waiter, table, products, payment method, and cancellations,...): about 30-40 secs.
Imagine how long it would take if we have 10000000 of rows (which could be realistic in a few years?)
Is there any way to improve this performance?
EDIT: I already solved it using a second table. As the table tbl_orders is used for the orders itself (recursive, for orders with sidedishes) i just put it joined into a new table tbl_report. There it is possible now for me to group things like i want them with good speed :)
Thank you for your advices. Some of them were helpful though :)
how should i mark my question? solved?