MySQL Database optimization

1. For tables using the MyISAM storage engine, use optimize table table_name to defragment the tables regularly.
2. For fields with high precision, use the decimal field type and try not to use the Float field type. Float is automatically rounded at the reserved bit
3.Mysql default storage engine is MyISAM, try to use MyISAM, if the application is read and insert operations, only a few update and delete operations, and the integrity of things, the concurrency requirements are not very high. Its advantage is the fast access.
InnoDB: Provides transaction security with commit, rollback, and crash resiliency. However, compared with MyISAM, the write processing efficiency is less, and will occupy a lot of disk space.
4. Date type uses: Date only date, DateTime can be stored date time and seconds;
However, the integer type is better int, the timestamp is stored, but int 4 bytes does not exceed 2038 years 1-29, 4 bytes means 2 of the 31 -1=2147483647,1 is the sign bit.
MySQL Date function: Date_sub find the time between changes in a date, such as finding a response within the last 30 seconds.
Date_add (now (), Interval 2 month) Current time plus 2 months later
New () Current time. Now () current time, interval minus second (sec)
For example, table Bbs_post structure: Id,title,content,pub_time field.
Query the last 30 seconds when the article was published: SELECT * from Bbs_post where Date_sub (now (), interval second)) <pub_time;

Time stamp automatic conversion in MySQL:mysql>select from_unixtime (1249488000, '%y year%m month%d ') ->2007 year November