Some practical tips for summarizing MySQL table-building and query optimization

This article is a MySQL table and query optimization of some practical tips for a detailed analysis of the introduction of the needs of friends under the reference

The MySQL table stage is a very important link, the table structure of good or bad directly affect the follow-up management and maintenance, to go to work before tomorrow to share the summary of Personal MySQL building table, MySQL query optimization accumulation of some practical tips.

Technique one, data table redundant record add time and update time
Many of the data tables we use will have a table record of "Add Time" (add_time), I suggest that you add a record "Update Time (update_time)" field, in my work needs for marketing, operations, and so on to establish a variety of reports, but very Multiple reports in the data are required to the large record table to query, if directly query large tables, the query speed is rather slow. Our solution, then, is to create snapshots of data, which are run by scripts. If, which day the original data table has the problem, then the snapshot data also can have the problem, this time our snapshot script will have to rerun a fixed data, thus has the Update_time update time This field, we can quickly locate the record update time.

Tip Two, do not discard insert ignore into and replace into
you must have written some script loops to insert records into a datasheet, such as using a PHP script. If one of the SQL inserts in the period fails, then the subsequent inserts are terminated and you can bypass the insertion error in the script, a good idea is to use the Ignore keyword to mask the insertion error. If your data table already has the same record that will be inserted, then an insert error will be generated at this time, and inserting ignore into will bypass the error and continue the insertion of the next record.

If your table structure is designed with a unique index, so it would be nice to use replace into to update your datasheet record, especially if you're writing a script, you don't need to query the data table for the record in the script, if it exists, then the update; You can use the replace into directly, it automatically to the datasheet detection, if the unique index character Gencun at this value, you will first delete the record, and then insert a new record; If the unique index field does not exist, inserting the datasheet directly is simple and very practical.

Tip Three, create a unique index to your table
in many cases, creating a unique index of the table can save us a lot of trouble, and think that the replace into must have a unique index, of course, Many people like to use the self-added primary key ID, although it is also a unique index, I suggest that it is possible to be in other fields that often appear in the where, and that the value is the only way to add an index or even a unique index, the query speed will be improved a lot. The

says at the end: Of course, insert ignore into and replace into everyone should be based on their own needs to determine, not necessarily suitable for your current needs; I just suggest that you have so posting practical little knowledge that everyone can flexibly apply to the project inside.