How to write High-performance PHP code (1)

Database optimization techniques, what do you do:

· Use a persistent connection database to avoid connection overhead. If you cannot use a persistent connection and you are starting many new connections to the database, you may want to change the value of the thread_cache_size variable. See section 7.5.2, "Tuning server parameters."

· Always check that all queries do use indexes that have already been created in the table. In MySQL , you can do it with the explain command. See section 7.2.1, "Explain syntax (get information about Select)".

· Try to avoid performing complex select queries on frequently updated tables to avoid problems with read and write conflicts that are related to locking tables.

· For MyISAM tables for rows that are not deleted, you can insert rows at the end while another query is reading from the table. If this is important, consider using the table in a way that avoids deleting rows. Another possibility is to run optimize TABLE after deleting a large number of rows. See section 15.1, "MyISAM storage engine."

· To fix any compression problems that can occur with any archive table, you can execute the Optimize table. See section 15.8, "Archive storage engine."

· If you mainly press EXPR1,EXPR2, ... Retrieve rows sequentially, using alter TABLE ... Order by EXPR1, Expr2, ..... Use this option after a large number of changes to the table to achieve better performance.

· In some cases, it makes sense to introduce a "hash" column based on information from columns from other tables. If the column is short and has a reasonable unique value, it can be faster than a large index on many columns. In MySQL , this extra column is easy to use:

· SELECT * from Tbl_name

· WHERE hash_col=md5 (CONCAT (col1,col2))

· and col1= ' constant ' and col2= ' constant ';

· For frequently changing MyISAM tables, you should try to avoid all variable-length columns (VARCHAR, blobs, and text). If the table includes a single variable length column, the dynamic record format is used. See Chapter 15th: Storage engine and table type.

· Just because the rows are too large, it is generally not useful to split a table into different tables. To access the row, the biggest performance impact is the disk search to find the first byte of the row. When data is found, most new types of disks are fast enough for most applications to read the entire row. The only thing that really needs to be split is if it's a MyISAM table that uses dynamic record format to make it a fixed record size (see above), or if you need to scan the table very frequently without requiring most columns. See Chapter 15th: Storage engine and table type.

· If you need to compute the results very often, for example, based on the count of information from many rows, it may be better to introduce a new table and update the counter in real time. The following forms of updates are faster:

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

This is really important when you use a MySQL storage engine that is just a table-level lock, like MyISAM, which is more accented/single. This also gives most databases better performance because the row lock manager has fewer things to do in this case.

         If you need to collect statistics from a large record file table, use a summary table instead of scanning the entire table. Maintenance summaries should be faster than trying to do "live" statistics. Regenerating a new summary table from the log when there is a change is much faster than changing the application (depending on the business decision). If possible, the report should be categorized as "live" or "statistical", where the data required for the statistical report is based solely on the summary tables generated from the actual data periodically. Take advantage of the fact that columns have default values. The value is inserted explicitly only when the value being inserted differs from the default value. This reduces the syntax analysis that MySQL needs to do to improve the insertion speed. In some cases, it is convenient to wrap and store data in a BLOB column. In this case, additional code must be added to your application to package/unpack the information, but this approach can save a lot of access at some stage. This is useful when there is data that does not conform to the row and list structure. In general, you should try to save the data in a non-redundant way (see the third regular form in the database theory), but to get faster, you can copy the information or create a summary table. Stored procedures or UDF (user-defined functions) can be a good way to get better performance, see 20th: Store programs and functions and section 27.2, "Add a new function for MySQL". You can always gain some benefits by caching queries/answers in your application and trying to perform many inserts/updates at the same time. If the database supports locking tables (like MySQL and Oracle), this should help ensure that the index cache is refreshed only once after all updates. You can also use MySQL's query cache to get similar results, see section 5.13, "MySQL query cache". Use Insert delayed when you do not need to know when to write data. This can be done faster because many records can be written to disk writing once. Use Insert/*! when you want to make the selection seem more important Low_priority * *. Use the Insert low_priority to retrieve the insertion queue, which is to perform a select even if another client waits for a write. Use a multiline INSERT statement to store many rows through an SQL command (many SQL Servers support it, including MySQL). Loads a larger amount of data using the load data infile. This is much faster than using inserts. Use the Auto_increment column to make a unique value. When MyISAM uses a dynamic table style, occasional optimize table is used to avoid fragmentation. See section 15.1.3, "storage format for MyISAM tables". When possible, use the memory table for faster speeds. See section 15.4, "MEMORY (HEAP) storage engine." In a Web server, images and other binary assets should be stored as files. This is the reference to this file that is stored only in the database, not the file itself. Most Web servers are much better at caching files than database content, so using files is generally much faster. Use memory tables for infrequently accessed unimportant data, such as information about the last banner displayed by a user who does not have cookies enabled in the Web browser. User sessions can also be used in many Web application environments to process mutable state data. Columns with the same information in different tables should be declared to be the same and have the same name. Try to simplify the name. For example, use name instead of customer_name in the Customer table. In order for the name to be ported to other SQL Servers, the name should be shorter than 18 characters. If you do need a high speed, you should look at low-level interfaces for data storage supported by different SQL Servers. For example, a direct access to the MySQL MyISAM storage engine can be up to 2-5 times faster than using the SQL interface. In order to be implemented, the data must be on the same server as the application, and typically should only be accessed by one process (because the external file lock is really slow). The introduction of low-level MyISAM commands on MySQL servers eliminates these problems (which may be an easy way to achieve better performance if needed). By carefully designing the database interface, you should be able to support such optimizations fairly easily. If you are using digital data, in many cases, accessing information from one database (using a live connection) is faster than accessing a text file. This is because the information in the database is more compact than the text file, so this will involve less disk access. You can also save code in your application because you do not have to parse text files to find the boundaries of travel and columns.

· Replication can improve the performance of some operations. You can distribute the retrieval of a customer in the replication server to divide the load evenly. To prevent the primary server from slowing down during backup, you can use a backup from the server. See Chapter 6th: Replication in MySQL.

· Declaring a MyISAM table with the delay_key_write=1 option can make the index update faster because they are not flushed to the hard disk until the table is closed. The downside is that if you kill the server when the table is open, be sure to run the server with the--myisam-recover option to ensure there are no problems, or run myisamchkbefore restarting the server. (However, even in this case, you should ensure that no data is lost by using delay_key_write because keyword information can always be generated from a data row).