Some Words about MySQL

# mysql -u root -p
Enter password: 

With data:

mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql

Without data:

mysqldump --no-data -u YourUser -p YourDatabaseName > wantedsqlfile.sql

mysql -u username -p database_name < file.sql

mysql> CREATE DATABASE <myDatabase>;

Query OK, 1 row affected (0.01 sec)

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

mysql> grant usage on *.* to myUsername@localhost identified by 'myPassword';
Query OK, 0 rows affected (0.10 sec)

mysql> grant all privileges on myDatabase.* to myUsername@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

# mysql -u myUsername -p
Enter password: 

mysql> use eatimeclock;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> show grants;

+———————————————————————————————————————+

+———————————————————————————————————————+

+———————————————————————————————————————+ 2 rows in set (0.01 sec)

mysql> exit
Bye

The first column should be:

• Field: ID_
• Type: INT or a variation of it!
• Null: NOT NULL
• Extra: AUTO_INCREMENT
• a PRIMARY KEY

example:

 CREATE TABLE `patient` (
`id_patient` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
...//Others instructions
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_swedish_ci

• ENUM

  `gender` ENUM( 'Male', 'Female', 'Unknown' ) NOT NULL

• INT/MEDIUMINT/TINYINT

  `npa` MEDIUMINT UNSIGNED NOT NULL COMMENT 'Postal Code',

• TIMESTAMP

  `lastmodification` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL

1. • Q: MySQL says: “This is not a number”
   • R: VARCHAR needs a “Length/Values”

     `firstname` VARCHAR( 40 ) NOT NULL

mysql> SET time_zone = 'Europe/Zurich';

If set time_zone fails with this error:

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Zurich'

you need to load the time zone info into mysql with a command like this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

For more info see http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

If this error,

mysql>SET GLOBAL show_compatibility_56 = ON;

mysql> SET time_zone = 'Europe/Zurich';

mysql> SELECT @@session.time_zone;
 
-> Europe/Zurich

Based on MySQL v5.7.

1. The lowest storage size for a row is a byte. The following declarations are not stored in one single bit.

   tinyint(1)
   bit(1)
   bit -- when the number is missing, mysql assuming bit(1)
   bool -- is just an alias for tinyint(1)
   boolean -- idem

   It's exactly the same with a ENUM. If you use a enum with 4 diffents values, don't think mysql will use only 2 bits. An ENUM use 1 byte (1 to 255 values), or 2 bytes (256 to 65535 values, but the practical limit is less than 3000).

2. The optional number between parenthesis after an integer type is called display width.

   tinyint(2)
   int(4)
   bigint(20)

   The display width does not constrain the range of values that can be stored in the column.
   To prove it:

   +-------------+--------------------------+------+-----+---------+----------------+
   | Field       | Type                     | Null | Key | Default | Extra          |
   +-------------+--------------------------+------+-----+---------+----------------+
   | id          | smallint    unsigned     | NO   | PRI | NULL    | auto_increment |
   | value_t_1   | tinyint(1) unsigned      | YES  |     | NULL    |                |
   +-------------+--------------------------+------+-----+---------+----------------+

   insert into test_tiny set value_t_1=123;

   +----+-----------+
   | id | value_t_1 |
   +----+-----------+
   |  1 |       123 |
   +----+-----------+

   But, if you have a

   tinyint (4) unsigned zerofill

   And you put a 3 in the row, you get:

   +----+-----------+
   | id | value_t_1 |
   +----+-----------+
   |  1 |      0003 |
   +----+-----------+

3. There is no rollover. If you insert 256 in a 8 bits unsigned value, Mysql donesn't store 1. If we try this (in the same table):

   mysql> insert into test_tiny set value_t_1=256;

   MySQL give you an error.

   ERROR 1264 (22003): Out of range value for column 'value_t_1' at row 1

4. For the primary key:

   tinyint unsigned -- 1 byte, 255 elements
   smallint unsigned -- 2 bytes, 65536 elements
   mediumint unsigned -- 3 bytes, approx. 16 millions elements (10E6)
   int unsigned -- 4 bytes, approx. 4 billions elements (10E9)
   bigint unsigned -- 8 bytes, approx. 9 quintillions elements (10E18)

   For all physical device, it's useless to use bigint. It's reasonable to assert that we don't install more than 16 millions sensors or centrals. And if it will be the case one day, the database will be splitted.
   So, for physical elements, mediumint is enough.
   For sample, the bigint can be justified.

5. For timestamp:

   int unsigned -- Overflow is on year 2'106
   bigint unsigned -- Overflow is on year 292'277'026'596 (easyControl in a Deathstar?)

   So, mediumint is enough for timestamp.

6. Double VS smallint:
   A double use 8 bytes, and a mediumint use 4 bytes.
   If you exactly know the range and the precision of the stored value, the good question to ask is: what is better between an integer scaled value (4 bytes, but division implied) and double value (8 bytes).
   
   Let's try it:
   With 600'000 sample in a table order by sampled (old version). We choose 600'000 values because it's the worst theoretical case: 1 measure per seconde during 7 days. When we displaying more than 7 days, MySQL make an average.

   select temperature from static_sample where sampled < 1473840000;
   600005 rows in set (2.13 sec) -- Average execution time (10 requests made), on my macMini

   select temperature/10000 from static_sample where sampled < 1473840000;
   600005 rows in set (2.25 sec) -- Average execution time (10 requests made), on my macMini

   To summarise: we save 50% in the storage size for the cost of 6% in the request execution time.
   But this test is with only one row. The result is not the same with 2 or more rows.
   
   Let's try a test closer to the actual reality:
   In practice, actually, the database is sorted by sensor_id. The sensor run a measure all 15 minutes. The best we can do with real data is a test with all sample from a sensor (38000).

   select temperature, warning_max, warning_min, critical_max, critical_min from static_sample where static_Sensor_id=246;
   38543 rows in set (0.085 sec) -- Average execution time (10 requests made), on server7

   select temperature/10000, warning_max/10000, warning_min/10000, critical_max/10000, critical_min/10000 from static_sample where static_Sensor_id=246;
   38543 rows in set (0.115 sec) -- Average execution time (10 requests made), on server7

   To summarise: we save 50% in the storage size for the cost of 36% in the request execution time.

If you want more informations, you can read Integer Types, Numeric type attributes and Data type storage.

• MySQL documentation
• Makes your data as small as possible
• Links on R937.com
• Managing Hierarchical Data in MySQL
• MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
• Mysql timezone