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?
Best Answer
Did putting the
IFNULL
back in slow it down again? If not, then it was an issue of caching, notIFNULL
.Assuming that
tt
will be looked at first, it needsRationale for the order:
status_id
is in theWHERE
and tested with=
; so put firstparent_id
is also in theWHERE
, but tested with a rangetennent_name, mall_name
are in different tables, so we cannot use any part of any index forORDER BY
Then assuming
mall_id
is thePRIMARY KEY
ofmall
, there is no use for any extra indexes on it.Putting
mall_id
in the index fortt
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 theWHERE
,status_id
, which smells like a flag or other low-cardinality key. So, I don't think the Optimizer will start withtm
.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...
is awfully messy. Let's clean it up first:
-->
Rather than counting how many, just check to see if 'any' exist.
Then, since
EXISTS()
returns 1 or 0, you don't need theCASE..WHEN..END
.Finally, get rid of the
IF(1=1...)
by adding a little bit of smarts as you build the query -- simply put0 AS is_favorite
in the query.Now, for the best index for
member_favorite
: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 thanmember_favorite
? If so, please qualify them so we can know what to do. TheEXPLAIN
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?