Mysql – Is a value that does not change calculated for each row

MySQLoptimization

Consider an SQL query with the following WHERE clause:

WHERE expires >= DATE_FORMAT(NOW(), '%Y%m%d')

Will the result of DATE_FORMAT() be recalculated for each row, or will it be calculated once and used for each row? If the latter, I can calculate the value in Python and then inject it into the SQL sent to MySQL.

Due to application restraints, the value is actually a LONGTEXT column and this question is a simplification of the problem. The data must be stored in this column type due to the EAV nature of the table.

This question pertains to MySQL 5.7, but if other database management systems, particularly SQLite or Postgres have different behaviour then I would appreciate knowing that as well. Thank you.

Best Answer

As long as it is run as a single transaction, the value will be re-used on each line, not recalculated. I'm not 100% sure about Postgres or SQL Lite but I'm fairly confident (98%) they will work the same.

In the past when I was more confused about this question, I would pre-fetch the date value to a variable and then reuse the variable in the where clause. Now I realize that was pointless unless I have more than one place in a longer set of statements where I would like to use the value.

As a test, move the calculated date value to the select clause and execute against a large table that takes more than a few seconds to return. You will notice that the first line will have the same date/time value as the last record.

PERSPECTIVE #1

When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.

I once answered three posts from one individual on this subject

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.

In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.

PERSPECTIVE #2

I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.

PERSPECTIVE #3

I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.

MySQL 5.6 – Update Row Cell with 0 Value from Closest Non-Zero Row

SET @var :=0;
UPDATE records
SET time= (@var :=CASE WHEN time = '0' THEN @var ELSE time END)
ORDER BY id;

Fiddle

Add DESC after ORDER BY if you want to update time with the time value of the next id.

Best Answer

Related Solutions

Mysql – Innodb UPDATE performance

PERSPECTIVE #1

PERSPECTIVE #2

PERSPECTIVE #3

MySQL 5.6 – Update Row Cell with 0 Value from Closest Non-Zero Row

Related Question