Home >Backend Development >PHP Tutorial >MySQL Manual Version 5.0.20-MySQL Optimization (4) (1) (5)_PHP Tutorial
MySQL Manual Version 5.0.20-MySQL Optimization (4) (1) (5)_PHP Tutorial
- WBOYOriginal
- 2016-07-13 17:03:27775browse
Generally, all redundant data should be saved (called "third normal form" in database theory). However, it may be cost-effective to copy some information or create summary tables for greater efficiency.
Stored procedures or UDFs (user-defined functions) may be more performant when performing some tasks. Still, when a database doesn't support these features, there are alternatives that can serve the purpose, even if they are a bit slower.
You can get the results from the query cache or the response, and then perform many insert and update operations together. If the database supports table locks (such as MySQL and ORACLE), then this ensures that the index cache only needs to be refreshed once after all update operations.
When you don’t need to know when the data is written to the table, you can use INSERT DELAYED. This increases speed because multiple records are written to disk simultaneously.
When you want the SELECT statement to have a higher priority than the insert operation, use INSERT LOW_PRIORITY.
Use SELECT HIGH_PRIORITY to skip the retrieval record queue, which means that even if other clients are about to write data, SELECT will be executed first.
Uses multiple record insertion format in one INSERT statement (supported by many databases).
Use LOAD DATA INFILE to import large amounts of data, which is faster than INSERT.
Use the AUTO_INCREMENT field to generate unique values.
Execute OPTIMIZE TABLE regularly to prevent fragmentation of MyISAM tables using dynamic record format. See "15.1.3 MyISAM Table Storage Formats" for details.
Use a HEAP table, it may improve speed. See "15.1.3 MyISAM Table Storage Formats" for details.
In a normal WEB server configuration, image files are best stored as files, and only the index information of the files is saved in the database. The reason for this is that usually the WEB server caches files better than the database does, so using file storage will make the system easier to make faster.