Summary of MySQL table building query optimization of some practical tips

The table building stage of MySQL is a very important link. The quality and quality of the table structure will directly affect the subsequent management and maintenance. Before going to work tomorrow, I would like to share and summarize some practical tips accumulated by my personal MySQL table building and MySQL query optimization.

Tip 1. Data table redundancy record add time and update time
We use a lot of data table in most cases there is a table record "add time (add_time)", I suggest you add a record "update time (update_time)" field, in my job need to establish various reports, such as marketing, operations, and a lot of the data in the report are need to go to the big record list of the query, if large query table directly, query speed is quite slow. Our solution, then, is to create various data snapshots, all of which are timed by scripts. If there is a problem with the original data table on a given day, then the snapshot data will also have a problem, at which point our snapshot script will have to run a repaired copy of the data again, so with the update_time update time field, we can quickly locate the record update time.

Tip 2. Don't abandon insert ignore into and replace into
You must have written some script loops to insert records into a data table, such as the PHP script. If one of the SQL inserts during this period fails, the subsequent inserts will terminate. You can bypass this insertion error in the script. A good way is to use the ignore keyword to mask the insertion error. If you already have the same record to insert in your table, an insert error will occur. insert ignore into will bypass this error and proceed with the next insert.

If your table structure is designed with a unique index, it is best to use replace into to update your table records, especially if you are writing a script, you do not need to check whether the data table exists in the script first. Insert if it doesn't exist. You can directly use replace into, which will automatically detect the data table. If the value exists in the only 1 index field, the record will be deleted first, and then a new record will be inserted. If the value does not exist for the only 1 index field, it is simple and useful to insert it directly into the data table.

Tip 3. Create a unique index for your table
In many cases to establish table 1 index, can save a lot of trouble to us, to the above said replace into, there must be only one index, of course, a lot of people like to use on the primary key ID although is only 1 index, my suggestion is that can often occurs in other behind where conditions to the back of the field, and value is only 1 can add index even if only 1 index, query speed will improve a lot.

In the end, of course, insert ignore into and replace into should be determined according to their own needs. I just want to remind you that there are a few practical tips that you can apply to your projects.