## 8.2.5.3 Optimizing DELETE Statements 

The time required to delete individual rows in a MyISAM  table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size  system variable. See Section 5.1.1, “Configuring the Server” . 

To delete all rows from a MyISAM  table, TRUNCATE TABLE tbl_name  is faster than DELETE FROM tbl_name . Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock. See Section 13.1.37, “TRUNCATE TABLE Statement” . 

## 8.2.6 Optimizing Database Privileges 

The more complex your privilege setup, the more overhead applies to all SQL statements. Simplifying the privileges established by GRANT  statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you do not grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv  and columns_priv  tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high statement-processing load, consider using a simplified grant structure to reduce permission-checking overhead. 

## 8.2.7 Other Optimization Tips 

This section lists a number of miscellaneous tips for improving query processing speed: 

•If your application makes several database requests to perform related updates, combining the statements into a stored routine can help performance. Similarly, if your application computes a single result based on several column values or large volumes of data, combining the computation into a loadable function can help performance. The resulting fast database operations are then available to be reused by other queries, applications, and even code written in different programming languages. See Section 25.2, “Using Stored Routines”  and Adding Functions to MySQL for more information. 

•To fix any compression issues that occur with ARCHIVE  tables, use OPTIMIZE TABLE . See Section 16.5, “The ARCHIVE Storage Engine” . 

•If possible, classify reports as “live”  or as “statistical” , where data needed for statistical reports is created only from summary tables that are generated periodically from the live data. 

•If you have data that does not conform well to a rows-and-columns table structure, you can pack and store data into a BLOB  column. In this case, you must provide code in your application to pack and unpack information, but this might save I/O operations to read and write the sets of related values. 

•With Web servers, store images and other binary assets as files, with the path name stored in the database rather than the file itself. Most Web servers are better at caching files than database contents, so using files is generally faster. (Although you must handle backups and storage issues yourself in this case.) 

•If you need really high speed, look at the low-level MySQL interfaces. For example, by accessing the MySQL InnoDB  or MyISAM  storage engine directly, you could get a substantial speed increase compared to using the SQL interface. 

Similarly, for databases using the NDBCLUSTER  storage engine, you may wish to investigate possible use of the NDB API (see MySQL NDB Cluster API Developer Guide). 

•Replication can provide a performance benefit for some operations. You can distribute client retrievals among replicas to split up the load. To avoid slowing down the source while making backups, you can make backups using a replica. See Chapter 17, Replication . 

