optimize big sql query

Question

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?

Answer 1

All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

• Use views and stored procedures instead of heavy-duty queries.
• Try to use constraints instead of triggers, whenever possible.
• Use table variables instead of temporary tables.
• Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard