Summary of mysql Optimization for a simple php website

Mysql Optimization-earlier

1. Table Structure

(A) Primary Key -- use a primary key with a short length, small memory usage, and an integer as much as possible

Tinyint27-128-127

Smallint215-32768-32767

Mediumint223-8388608-8388607

Int231

Bigint263

(B) Foreign keys-we recommend that you remove foreign keys for batch reliable data insertion.

(C) index-create an appropriate number of indexes for the appropriate columns in the appropriate table to balance the query priority and update priority.

Suitable for adding indexes: Adding indexes to columns that frequently need to be searched can accelerate the search speed.

Force the uniqueness of the column on the column as the primary key and arrange the data in the organization table

Create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous.

Create an index on a column that is frequently used for sorting

Create an index on a column in the where clause

Not suitable for adding indexes: No index is added for columns that use a small number of queries.

Few data values are not indexed

Columns defining text, image, and bit are not indexed.

When the modification performance is far higher than the retrieval performance, no joint index is added.

Federated index: in a specific query, the Union index is more effective than a single index.

Index length: For varchar, char creates an index with a part of the column

(D) using a non-transaction table -- myisam queries far faster than innodb

2. Table Fields

(A) enum, tinyint, and time type are preferred. The timestamp and ip address are stored in int.

(B) Shorten the field length as much as possible

(C) notnull is preferred. By default, null or special characters are used.

3. script program

(A) Do not use high-consumption SQL statements such as like and join,

(B) The where clause should contain as few fields as possible, and indexed columns should be used first.

(C) do not use as few query fields as possible *

This article is from the "doYouUsePHP" blog, please be sure to keep this source http://junay.blog.51cto.com/6809902/1300491