CASE performance in MySQL?

Question

Interestingly, if you use the expression CASE ... WHEN ... THEN in MySQL queries
adversely affect performance?

Instead of using a CASE expression (e.g. inside your UPDATE query)
you always have the opportunity to make an if else expression in your program
written in php, python, perl, java, ... to select a request to send, for example (in pseudocode):

prepareStatement( "UPDATE t1 SET c1=c1+1, msg=CASE (@v:=?) WHEN '' THEN msg ELSE @v END" ); setStatementParameter(1, message); 

or insead:

 if (message == "") { prepareStatement("UPDATE t1 SET c1=c1+1"); } else { prepareStatement("UPDATE t1 SET c1=c1+1, msg=?"); setStatementParameter(1, message); } 

(c1 is simply needed here to show that something happens in both cases)

Which way to do this has the best performance?
And how much is the execution penalty?

Answer 1

All the functions of each line will affect performance, the only real question is: "Is the effect small enough not to worry?" This is what you should discover by measuring, not guessing. Administering a database is just the “Block and Forget” action if neither your data nor your queries ever change.

Otherwise, you should periodically monitor the performance so that there are no problems.

By “small enough” in the comments above, I mean, you probably don't need to worry about the impact of performance on something like:

 select * from friends where lowercase(lastname) = "smith" 

if you have only three friends.

The effect of these things becomes more serious as the size of the table increases. For example, if you have one hundred million customers and you want to find everything that can be connected to a computer, you will not want to try:

 select name from customers where lowercase(name) like '%comp%' 

This can cause your database administrators to be on you like a ton of bricks.

One of the ways we fixed in the past is to introduce redundancy in the data. Using this first example, we will add an additional column named lowerlastname and populate it with the string value lastname . An index that, for search purposes and your select statements, becomes dazzlingly fast, as it should be.

And as for our beloved 3NF, I hear what you ask? The answer is nothing if you know what you are doing :-)

You can configure the database so that this new column is populated with the insert / update trigger to maintain data consistency. This is perfectly acceptable for breaking the 3NF for performance reasons, if you understand and mitigate the consequences.

Similarly, this second query may have an insert update trigger that populates a new indexed column name_contains_comp whenever an entry containing the corresponding text has been updated or inserted.

Since most databases are read much more often than they are written, this transfers the cost of calculation to insert / update, effectively depreciating it in all selected operations. Then the query will look like this:

 select name from customers where name_contains_comp = 'Y' 

Again, you will find the query dazzlingly fast at a negligible cost for slightly slower inserts and updates.