code prettify

Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Sunday, 26 June 2016

MySQL auto update date_created and date_updated columns

It's usually a good practice to have two columns called date_created and date_updated in every table. One can always use it in application and it helps in debugging too as to when a record was created and last updated in various circumstances and contexts.




This responsibility can be given to MySQL to automatically assign current time values to these columns.

In MySQL 5.6 onwards, this can be done by setting the data type of the columns to be either date time or timestamp and creating date_created column with NOT NULL DEFAULT CURRENT_TIMESTAMP  schema and date_updated column with NOT NULL DEFAULT '0000-00-00 00:00:00' as schema with attribute ON UPDATE CURRENT_TIMESTAMP.

Below is a sample schema of a table containing date_created and date_updated columns:

CREATE TABLE `time_stamp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

But there is a catch here, since this is not applicable to all MySQL versions. Earlier to 5.6 version, MySQL allows a table to have only one TIMESTAMP column with an automatic TIMESTAMP value, that is you can either have date_created or date_updated auto updated to CURRENT_TIMESTAMP not both.

Related to this the MySQL documentation has some very good info at this page: https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

Excerpt: "By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp."

So the workaround this is to have date_created column with timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' schema  and date_updated column with timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP schema.

With above, the date_updated field is correctly updated by MySQL automatically when there is an update and to update date_created field with current timestamp value, we have to explicitly pass NULL value to date_created field which will then store the CURRENT_TIMESTAMP value in the field.

Below is a sample schema for above changes:

CREATE TABLE `time_stamp` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

So for a structure as above the insert query would be as below:

INSERT INTO `test`.`time_stamp`
(`id`, `date_created`) 
VALUES
(NULL, NULL);

For update the query would be:

UPDATE 
    `test`.`time_stamp` 
SET 
    `id` = '4' 
WHERE 
    `time_stamp`.`id` = 1;

Above will just update the date_updated with the CURRENT_TIMESTAMP and the date_created value will remain same as earlier. Caution needs to be taken while updating records so as not update date_created field with NULL value.

One could also say that instead of MySQL automatically updating the date_updated field, I would like the date_created field to be updated automatically by it. Yes, it is just a reverse case and can be used, in which case while updating the record one has to pass NULL value to date_updated field. But the thing is that since there will be many updates and only a one time insert so passing a NULL while a one time insert reduces the overhead on us of passing a NULL for every subsequent update and let MySQL handle it for us. But yes this is debatable and there are cases where this reverse structure can be used.

Another workaround this is to have date_created field updated with current timestamp value using  a trigger when a record is inserted.

Hope this helps :)

Sunday, 23 August 2015

Importance of MySQL cache

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:
  1.     How useful is MySQL cache?
  2.     When to use it and when not to use it?
  3.     What to do if you do not want to use it?

There are some catchy areas here too like not all your queries will be stored in cache. For example, a query executed inside a stored function will not be stored in cache. Below doc link from MySQL further details out the specifics.

https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

Starting with our goals:

1. How useful is MySQL cache?

It is very useful indeed as once you have the result data set, if the same query is executed again, the data is now fetched from the cache and it is much faster.

2. When to use it and when not to use it?


As per MySQL doc "If a table changes, all cached queries that use the table become invalid and are removed from the cache." https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html .

So if you have executed a query and any content in any of the tables used in the query has changed (whether related or not to the dataset for the query), all the cache entries using those tables will be flushed and executing the same query again will fetch the data from the database.

So is this bad or is fine? Depends :).

If the query you are executing is very often executed lets say 5-6 times every minute and the related tables in database are altered very often lets say at a similar rate 5-6 times a second, so we even if all the queries are stored in cache but are ultimately flushed when related table data is changed, leading to fetching once again from database and storing in cache, but which we are never able to utilize.

So in this case if the table data is too often changed then it is better not to use cache for related queries since using cache adds a certain overhead (13% as stated below) to MySQL.

https://dev.mysql.com/doc/refman/5.5/en/query-cache.html

"If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower."

Otherwise if the table is less fairly changed and the query fetching the data is more frequent then it is more efficient to use cache.

3. What to do if you do not want to use it?


As discussed above, if you do not want to use it for a query which is not able to benefit the cache storage use SQL_NO_CACHE option while executing the query
which tells MySQL not to store the result set in cache.

Eg. SELECT SQL_NO_CACHE id, name FROM customer;

https://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html