Summary of some useful tips for MySQL table creation and Query Optimization

The MySQL table creation stage is a very important step. The quality and advantages of the table structure directly affect the subsequent management and maintenance, I will share some practical tips to summarize my accumulation of MySQL tabulation and MySQL query optimization before going to work tomorrow.

Tip 1: add time and update time for redundant data table records
Many of the data tables we use usually have the "add_time" field recorded in the table. I suggest you add another "update_time" field for the record, in my work, I need to create various reports for the marketing department and operation department. In many reports, the data needs to be queried in the big record table. If I directly query the big table, the query speed is quite slow. Then, our solution is to create various data snapshots, and the data snapshots are regularly run through scripts. If there is a problem with the original data table on which day, there will also be a problem with the snapshot data. At this time, our snapshot script will have to re-run the repaired data, with the update_time Update time field, we can quickly locate the Record Update time.

Tip 2: Do not discard insert ignore into and replace
You must have written some scripts to insert records into data tables in a loop, such as using PHP scripts. If an SQL statement fails to be inserted during this period, subsequent Inserts will be terminated. You can bypass this insertion error in the script. A good way is to use the ignore keyword to block insertion errors. If the same record to be inserted already exists in your data table, an insert error will be generated. Using insert ignore into will bypass this error and insert the next record.

If your table structure has a unique index, it would be better to use replace into to update your data table records, especially when you write a script, you do not need to query the existence of the record in the data table in the script. If the record exists, it is updated. If the record does not exist, it is inserted. You can directly use replace into, which will automatically detect the data table. If this value exists in a unique index field, the record is deleted first, and then a new record is inserted; if this value does not exist in the unique index field, insert the data table directly, which is simple and practical.

Tip 3: create a unique index for your table
In many cases, creating a unique index can save us a lot of trouble. The replace into mentioned above must have a unique index. Of course, many people prefer the auto-increment primary key ID, which is also a unique index. I suggest using other fields that often appear behind the ass of the where condition, if the value is unique, an index or even a unique index can be added, and the query speed will increase significantly.

At the end: Of course, the insert ignore into and replace into should be determined based on your own needs, which is not necessarily suitable for your current needs; I just want to remind you that there is still some practical little knowledge that you can use it flexibly in the project.