Mysql Query really slow

Question

just tested today with large datasets. and my query really perform poorly, i suspect because of the subquery. here is my query (MYSQL PDO:)

SELECT  a.data_id,a.data_name,a.data_parent_id,a.data_type,a.data_return_text,a.data_description,
IF ((length(a.data_id)<=1), 0,
CASE a.data_type
WHEN '0' 
THEN 
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '1' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '2' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '3' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '4' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '5' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '6' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '7' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '8' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '9' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '10' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '11' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '12' THEN 
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
WHEN '13' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '14' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND client_id=:client_id),0)
WHEN '15' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '0' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('1','0') AND (DATE(trans_date) <= DATE(NOW())) AND client_id=:client_id),0)
WHEN '16' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND (DATE(trans_date) <= DATE(NOW())) AND client_id=:client_id),0)
WHEN '17' THEN
IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)
END) as total 
FROM data_list a
LEFT JOIN data_transaction_log b
ON b.trans_data_id = a.data_id
WHERE a.client_id=:client_id
GROUP   BY a.data_name
ORDER BY a.data_id asc

[Description]

There is table:

1. table:data_list

contains about 24 instruments which the instruments will create a transaction log when it is in use. it will result a (value) and its (+ve or -ve sign) and its date stamp

2. table:data_transaction_log

This table will log every result from instruments in data_list.

Result from table:data_list will be log inside this table:

• (value) => trans_value
• (+ve sign will record as 0 & -ve sign will record as 1) => trans_type
• (date) => trans_date

[Intention]

1. I would like to get sum of +ve & -ve value of each transaction for each instrument:

   IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)

and some instrument have special conditions (case 15 & 16) which requires date conditions.

IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND (DATE(trans_date) <= DATE(NOW())) AND client_id=:client_id),0)

[Problems}

On low data sets about 100 (in data_transaction_log) it works ok. but more than 1k it is really slow!

please guide me.

thanks!

Answer 1

For the data_list datatype field, there are the same exact results for multiple values. For example, WHEN a.data_type IN ('0', '1', '2', '3', '7', '9', '10', '12', '17')

you have the same result:

IFNULL((SELECT SUM(trans_value * case trans_type when '1' then -1 else 1 end) FROM data_transaction_log WHERE trans_data_id = a.data_id and trans_type in ('0','1') AND client_id=:client_id),0)

I don't think it will make the code faster but it is less code to write and easier to debug:

SELECT a.data_id,a.data_name,a.data_parent_id,a.data_type
      ,a.data_return_text,a.data_description
      ,CASE WHEN length(a.data_id)<=1) 
             THEN 0

            WHEN a.data_type IN ('0', '1', '2', '3', '7', '9', '10', '12', '17')
             THEN IFNULL((SELECT SUM(trans_value * case WHEN trans_type = '1' THEN -1 else 1 end) 
                            FROM data_transaction_log 
                           WHERE trans_data_id = a.data_id and trans_type in ('0','1') 
                             AND client_id=:client_id)
                           ,0)            

            WHEN a.data_type IN ('4', '5', '6', '8', '11', '13', '14')
             THEN IFNULL((SELECT SUM(trans_value * case WHEN trans_type = '0' then -1 else 1 end) 
                            FROM data_transaction_log 
                           WHERE trans_data_id = a.data_id and trans_type in ('0','1') 
                             AND client_id=:client_id)
                           ,0)

            WHEN a.data_type IN ('15', '16') 
             THEN IFNULL((SELECT SUM(trans_value * case WHEN trans_type = '0' then -1 else 1 end) 
                            FROM data_transaction_log 
                           WHERE trans_data_id = a.data_id and trans_type in ('0','1') 
                             AND (DATE(trans_date) <= DATE(NOW())) AND client_id=:client_id)
                           ,0)

       END as total 
  FROM data_list a LEFT JOIN data_transaction_log b ON b.trans_data_id = a.data_id
 WHERE a.client_id=:client_id
GROUP BY a.data_name
ORDER BY a.data_id asc

--

2nd Take:

To work on this some more, it would be good to know why you are checking for IFNULL. If it's because of the left join returning NULLs where the joining conditions don't match, then you may be able to simply the algorithm one more step by checking the primary key in the left-joined table, data_transaction_log, for NULL... the primary key should not be null, if it is null then it must be NULL by left join. In the original algorithm you gave NULL results the value of 0:

SELECT a.data_id,a.data_name,a.data_parent_id,a.data_type
      ,a.data_return_text,a.data_description
      ,CASE WHEN b.<PRIMARYKEY> IS NULL
             THEN 0
            WHEN length(a.data_id)<=1) 
             THEN 0    
            WHEN a.data_type IN ('0', '1', '2', '3', '7', '9', '10', '12', '17')
             THEN (SELECT SUM(trans_value * case WHEN trans_type = '1' THEN -1 else 1 end) 
                     FROM data_transaction_log 
                    WHERE trans_data_id = a.data_id and trans_type in ('0','1')                             AND client_id=:client_id)                      

            WHEN a.data_type IN ('4', '5', '6', '8', '11', '13', '14')
             THEN (SELECT SUM(trans_value * case WHEN trans_type = '0' then -1 else 1 end) 
                     FROM data_transaction_log 
                    WHERE trans_data_id = a.data_id and trans_type in ('0','1') 
                      AND client_id=:client_id)                   

            WHEN a.data_type IN ('15', '16') 
             THEN (SELECT SUM(trans_value * case WHEN trans_type = '0' then -1 else 1 end) 
                     FROM data_transaction_log 
                    WHERE trans_data_id = a.data_id and trans_type in ('0','1') 
                      AND (DATE(trans_date) <= DATE(NOW())) AND client_id=:client_id)                  

       END as total 
  FROM data_list a LEFT JOIN data_transaction_log b ON b.trans_data_id = a.data_id
 WHERE a.client_id=:client_id
GROUP BY a.data_name
ORDER BY a.data_id asc