Several optimization methods of MySQL

Optimizing MySQL at compile time
If you install MySQL from the source code, note that the compilation process will have an important impact on the performance of future target programs, different compilation methods may get similar target files, but performance may vary greatly, therefore, in the compilation and installation of MySQL adapt carefully according to your application type choose the most likely good compilation options. This custom MySQL can provide the best performance for your application.
Tip: Use better compilers and better compiler options, which can improve performance 10-30%. (as the MySQL document says)

1.1. Use PGCC (Pentium GCC) compiler
The compiler (http://www.goof.com/pcg/) is optimized for programs running on the Pentium processor system and compiles MySQL source code with PGCC, which can improve overall performance by 10%. Of course, if your server is not using a Pentium processor, you will not need to use it, because it is designed for the Pentium system.

1.2, only use the character set you want to compile MySQL
MySQL currently offers up to 24 different character sets for global users to insert or view data in their own language. But save the case, MySQL installs owner these character sets, hot however, the best choice is to choose one you need. For example, prohibit all other character sets except the latin1 character set:
------------------------------------------------------------------------------
%>./configure-with-extra-charsets=none [--other-configuration-options]
------------------------------------------------------------------------------

1.3. Compile the mysqld into a static execution file
You can get better performance by compiling mysqld into a static execution file without having to share a library. You can statically compile mysqld by specifying the following options at configuration time.
------------------------------------------------------------------------------
%>./configure-with-mysqld-ldflags=-all-static [--other-configuration-options]
------------------------------------------------------------------------------

1.4. Configuration Sample
The following configuration commands are often used to improve performance:
------------------------------------------------------------------------------
%>cflags= "-o6-mpentiumpro-fomit-frame-pointer" CXX=GCC cxxflags= "-o6-mpentiumpro-fomit-frame-pointer- Felide-constructors-fno-exceptions-fno-rtti "./configure--prefix=/usr/local--enable-assembler-- With-mysqld-ldflags=-all-static--disable-shared
------------------------------------------------------------------------------

Second, adjust the server
It is important to make sure that the correct compilation is used, but this is only the first step in the success, and the configuration of many MySQL variables is also critical to the normal operation of the server. You can assign these variables to a configuration file to make sure they work every time you start MySQL, which is the my.cnf file.
MySQL has provided a sample of several my.cnf files, which can be found in the/usr/local/mysqld/share/mysql/directory. These files are named My-small.cnf, MY-MEDIUM.CNF, my-large.cnf, and my-huge.cnf, and the scale description can be found in the system type header that describes the configuration file. If you run MySQL on a system with fairly little memory, and only occasionally, then MY-SMALL.CNF is ideal because it commands mysqld to use only the least resources. Similarly, if you plan to build an E-commerce supermarket and the system has 2g of RAM, you might want to use mysql-huge.cnf files.
To make use of one of these files, you need to copy a file that is best suited to your needs, renamed MY.CNF. You can choose to use a profile of three kinds of scopes:
Global: Copy the my.cnf file to the server's/etc directory, which makes the variables in the configuration file work globally, which is valid for MySQL database servers on all servers.
Local: Copy the my.cnf file to the [mysql-install-dir]/var/directory, making the MY.CNF work on a specific server. [Mysql-install-dir] represents the MySQL installation directory.
User: You can limit the my.cnf to a specific user, and copy the user's root directory.
How do you set these variables in the my.cnf? Further said, you can set which variable. Although the variables used are relatively generic to the MySQL server, each variable has a more specific relationship to some of the components of MySQL. If the variable max_connects is grouped under the mysqld category. You can know by executing the following commands:
------------------------------------------------------------------------------
%>/usr/local/mysql/libexec/mysqld--help
------------------------------------------------------------------------------
It displays a large number of options and variables related to mysqld. You can easily find variables under the line text:
------------------------------------------------------------------------------
Possible variables for option--set-variable (-O) are
------------------------------------------------------------------------------
You can then set the variables in the MY.CNF as follows:
------------------------------------------------------------------------------
Set-variable = max_connections=100
------------------------------------------------------------------------------
It sets the maximum number of concurrent connections for the MySQL server to 100. Be sure to insert the variable settings under the [Mysqld] heading in the my.cnf file.