MySQL - if condition optimization
NickName:mesibo Ask DateTime:2016-06-17T15:21:19

MySQL - if condition optimization

Just wondering, how this query will be handled by MySQL, will sum() calculated twice if sum(credits) != NULL or does MySQL has optimization in place for such queries. 

select if(sum(credits)=NULL, 0, sum(credits)) from ......

Thanks

Copyright Notice：Content Author:「mesibo」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/37875463/mysql-if-condition-optimization

Answers
Gordon Linoff 2016-06-17T07:22:33

If won't work. The first condition is always false. The correct way to compare to NULL is IS NULL, not =.\n\nJust use this construct:\n\nselect coalesce(sum(credits), 0)\n",

zakhefron 2016-06-17T07:28:16

Using IF\n\nSELECT IF(sum(credits) IS NULL, 0 ,sum(credits)) ..... \n\n\nIF(expr1,expr2,expr3)\n\nIf expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used \n\nUsing IFNULL\n\nSELECT IFNULL(sum(credits), 0) ..... \n\n\nUsing COALESCE\n\nSELECT COALESCE(sum(credits), 0) ..... \n\n\nRefer : MySQL Control Flow Functions",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “MySQL - if condition optimization” related questions
MySQL - if condition optimization

Just wondering, how this query will be handled by MySQL, will sum() calculated twice if sum(credits) != NULL or does MySQL has optimization in place for such queries. select if(sum(credits)=NULL,...

Show Detail
Is SQL Server support index condition pushdown?

MySQL supports index condition pushdown optimization. Does SQL Server support it? I didn't find any information on Google. MySQL Server - Index Condition Pushdown Optimization Index Condition Push...

Show Detail
MYSQL: used variable on IN condition

[EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results int...

Show Detail
How to make MySQL "honestly" "execute the query, without optimization, with all subqueries?

How to make MySQL "honestly" "execute the query, without optimization, with all subqueries? There is a SELECT query containing a subquery, if there are no records on the condition of the main query...

Show Detail
mysql order by multiple column optimization

I'm trying to learn the 'order by optimization' of mysql, so i search the related topic and find the official doc of mysql8 says : A condition for index use is that the index must have the same

Show Detail
MySQL Index Optimization

I'm struggling with MySQL index optimization for some queries that should be simple but are taking forever. Rather than post the specific problem, I wanted to ask if there is an automated way of de...

Show Detail
Optimization MySQL for large table

Question concerning MySQL and optimization over large table. The MySQL server is running on a limited capacity server and we need to optimize it as much as possible. We are sampling data at a rate...

Show Detail
MySQL Distinct Query with Subqueries optimization

I'm trying to take "Galt Barber on August 26 2006" advice here on distinct optimization: http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html My query looks like: SELECT COUNT( `

Show Detail
MySQL parallel execution for date range count() query do not give performance optimization

DB: MySQL 5.7 Table Engine: ENGINE=InnoDB Query: SELECT count(*) as COUNT FROM Data d WHERE d.StartDate &gt;= ? AND d.StartDate &lt; ? AND d.EntityID IN (1245) AND d.Condition01 &gt; 0&#x

Show Detail
MySQL 8.0 Index condition pushdown (ICP) does not take effect

Recently I have considered how Index condition Pushdown work, and whether make a difference of locking in Repeatable read isolation level. However, I find ICP does not take effect in MySQL 8.0, but...

Show Detail