Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL

Question

I have one SP which generates a final query (like the one below) which is taking too much time to execute.

Basically it has the following parts:

• IFNULL to check that if value is null then send blank value.
• IF statement to send blank value if logo/banner image is blank.

• Sub-Query to check the favorite related logic (1=1 , there will be a case when I will pass it like 0=1 to avoid the execution of Sub-Query when that result is not needed)

  SELECT
      IFNULL(tt.unique_code,'') AS unique_code,
      IFNULL(tt.tenant_name,'') AS tenant_name,
      IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/thumb/',IFNULL(tt.logo,''))),'') AS logo,
      IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/normal/',IFNULL(tt.logo,''))),'') AS logo_normal,
      IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/large/',IFNULL(tt.logo,''))),'') AS logo_large,
      IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/large/',IFNULL(tt.banner_image,''))),'') AS banner_image,
      IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/normal/',IFNULL(tt.banner_image,''))),'') AS banner_normal,
      IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/thumb/',IFNULL(tt.banner_image,''))),'') AS banner_thumb,
      IFNULL(tm.mall_code,'') AS mall_code,
      IFNULL(tm.mall_name,'') AS mall_name,
      CONCAT(IFNULL(tm.mall_name,''),', #',IFNULL(tt.floor_no,''),'-',IFNULL(tt.unit_no,'')) AS location,
      IFNULL(tt.opening_hours,'') AS opening_hours,
      get_date_string(NOW(),1,1,'%H:%i:%s') AS cur_time,
      get_date_string(NOW(),1,1,'%a') AS cur_day,
      IF(1=1,CASE WHEN ( SELECT COUNT(*) AS count_fav FROM member_favorite WHERE member_id = 170 AND ref_table_id = 2 AND ref_table_rec_id=get_ref_id(unique_code,2) ) = 1 THEN 1 ELSE 0 END,0) AS is_favorite -- i am calling sub query only if there is a need of is_favorite param in API, so 1 will be provided by API 
  FROM tenant AS tt
      LEFT JOIN mall AS tm
      ON tt.mall_id = tm.mall_id
  WHERE tt.status_id = 1
      AND tm.status_id != 0
      AND tt.parent_id > 0
  ORDER BY tt.tenant_name ASC, tm.mall_name ASC
  

  ---

  (493 row(s) returned)
  Execution Time : 00:00:00:375
  Transfer Time  : 00:00:01:688
  Total Time     : 00:00:02:063
  

If I just remove the ifnull related statement then the performance increases a little bit.

    SELECT
        /*IFNULL(tt.unique_code,'') AS unique_code,
        IFNULL(tt.tenant_name,'') AS tenant_name,
        IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/thumb/',IFNULL(tt.logo,''))),'') AS logo,
        IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/normal/',IFNULL(tt.logo,''))),'') AS logo_normal,
        IF(IFNULL(tt.logo,'') !='',(CONCAT('https://site.com/uploads/','store/logo_image/large/',IFNULL(tt.logo,''))),'') AS logo_large,
        IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/large/',IFNULL(tt.banner_image,''))),'') AS banner_image,
        IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/normal/',IFNULL(tt.banner_image,''))),'') AS banner_normal,
        IF(IFNULL(tt.banner_image,'') !='',(CONCAT('https://site.com/uploads/','store/banner_image/thumb/',IFNULL(tt.banner_image,''))),'') AS banner_thumb,
        IFNULL(tm.mall_code,'') AS mall_code,
        IFNULL(tm.mall_name,'') AS mall_name,
        CONCAT(IFNULL(tm.mall_name,''),', #',IFNULL(tt.floor_no,''),'-',IFNULL(tt.unit_no,'')) AS location,
        IFNULL(tt.opening_hours,'') AS opening_hours,*/
        get_date_string(NOW(),1,1,'%H:%i:%s') AS cur_time,
        get_date_string(NOW(),1,1,'%a') AS cur_day,
        IF(1=1,CASE WHEN ( SELECT COUNT(*) AS count_fav FROM member_favorite WHERE member_id = 170 AND ref_table_id = 2 AND ref_table_rec_id=get_ref_id(unique_code,2) ) = 1 THEN 1 ELSE 0 END,0) AS is_favorite -- i am calling sub query only if there is a need of is_favorite param in API, so 1 will be provided by API 
    FROM tenant AS tt
        LEFT JOIN mall AS tm
        ON tt.mall_id = tm.mall_id
    WHERE tt.status_id = 1
        AND tm.status_id != 0
        AND tt.parent_id > 0
    ORDER BY tt.tenant_name ASC, tm.mall_name ASC  

---

    Result in SQL Yog
    --
    (493 row(s) returned)
    Execution Time : 00:00:00:375
    Transfer Time  : 00:00:00:578
    Total Time     : 00:00:00:953

I have tried to apply the index but didn't achieve much benefit in performance. Below is the explain part of query:

        id  select_type         table            partitions  type    possible_keys                                                                      key                 key_len  ref                       rows  filtered  Extra                                       
------  ------------------  ---------------  ----------  ------  ---------------------------------------------------------------------------------  ------------------  -------  ----------------------  ------  --------  --------------------------------------------
     1  PRIMARY             tm               (NULL)      ALL     PRIMARY,index_status_id                                                            (NULL)              (NULL)   (NULL)                       4    100.00  Using where; Using temporary; Using filesort
     1  PRIMARY             tt               (NULL)      ref     index_status_id,index_mall_id,index_parent_id,index_composite_status_id_parent_id  index_mall_id       3        dbLendLease.tm.mall_id     129     80.56  Using where                                 
     2  DEPENDENT SUBQUERY  member_favorite  (NULL)      ref     index_ref_table_id,index_member_id                                                 index_ref_table_id  8        func,const                   2      1.83  Using where                                 

So what changes should I make to improve this query?

Answer 1

Did putting the IFNULL back in slow it down again? If not, then it was an issue of caching, not IFNULL.

WHERE tt.status_id = 1
  AND tm.status_id != 0
  AND tt.parent_id > 0
ORDER BY tt.tenant_name ASC, tm.mall_name ASC  

Assuming that tt will be looked at first, it needs

INDEX(status_id, parent_id) -- in this order

Rationale for the order:

• status_id is in the WHERE and tested with =; so put first
• parent_id is also in the WHERE, but tested with a range
• tennent_name, mall_name are in different tables, so we cannot use any part of any index for ORDER BY

Then assuming mall_id is the PRIMARY KEY of mall, there is no use for any extra indexes on it.

Putting mall_id in the index for tt is useless -- unless it is not the first table to be looked at.

Case 1: You really need LEFT because the rows are optional in the 'right' table. In this case, the Optimizer must start with the 'left' table.

Case 2: You don't need LEFT. In this case, the Optimizer could start with either table. It is sometimes best to provide indexes for either ordering.

Now, let's study starting with tm. The first thing needs to be the only thing in the WHERE, status_id, which smells like a flag or other low-cardinality key. So, I don't think the Optimizer will start with tm.

I don't see any good argument for a "covering" index for either table.

Back to the one index I recommended -- It may be counter-productive. tt.status_id = 1 AND tt.parent_id > 0: If the status is usually 1 and the parent is usually >0, then my composite index has too low a cardinality, hence the bouncing back and forth between the index's BTree and the data's BTree may slow down the query.

Not to dispair, there is still one more thing...

 IF(1=1,CASE WHEN 
            (
                SELECT  COUNT(*) AS count_fav
                    FROM  member_favorite
                    WHERE  member_id = 170
                      AND  ref_table_id = 2
                      AND  ref_table_rec_id=get_ref_id(unique_code,2) 
            ) = 1 THEN 1 ELSE 0 END,
                0
   ) AS is_favorite

is awfully messy. Let's clean it up first:

SELECT COUNT(*) ... = 1

-->

EXISTS ( SELECT 1 FROM ... )

Rather than counting how many, just check to see if 'any' exist.

Then, since EXISTS() returns 1 or 0, you don't need the CASE..WHEN..END.

Finally, get rid of the IF(1=1...) by adding a little bit of smarts as you build the query -- simply put 0 AS is_favorite in the query.

Now, for the best index for member_favorite:

INDEX(member_id, ref_table_id,   -- in either order
      ref_able_rec_id)

Where is get_ref_id defined? Perhaps it is a sloooow Stored Function that we need to look at?

Are any of the columns mentioned in the EXISTS in some table other than member_favorite? If so, please qualify them so we can know what to do. The EXPLAIN says "dependent", but we don't know what part depends!

Looking at the EXPLAIN, I see only 4 rows. No big deal.

What does 00:00:00:375 mean? Is that a non-standard notation for 375ms?

Note how the 'execute' time is the same, but the transfer time is different. Well, the query with the IFNULLs is not any slower to execute but there is more data to send back. This is as could be expected. How many bytes are returned?

Answer 2

You have used number of columns from tenant AS tt and mall AS tm tables and they all should be used in the multicolumn indexes.

. . . . . 
 FROM tenant AS tt
 LEFT JOIN mall AS tm ON tt.mall_id = tm.mall_id
WHERE tt.status_id = 1
  AND tm.status_id != 0
  AND tt.parent_id > 0
ORDER BY tt.tenant_name ASC, tm.mall_name ASC  
. . . . . 

You need something like that: (mall_id, status_id, mall_name) for the mall table and (mall_id, status_id, parent_id, tenant_name) for tenant table.

Exact order of the columns in the indexes can vary due to the real data in your tables.