—There are four areas that you can tune in MySQL
1.SQL tuning
2.Schema and Index tuning
3.mysqld tuning
4.Operating system tuning
One word of warning some variables are specified in bytes while other maybe specified in megabytes, also some can be changed dynamically others will require a restart of MySQL, so it is always best to double check the MySQL documentation.
You have three ways on which to configure a system variable
my.cnf file (my.ini file in windows)
when starting mysqld from the operating system commandline
using the set global mysql command
My preference is using the my.cnf file, here you can see what variables have been changed, you can also add comments relating back to change controls on why a variable was changed. The configuration file supports options from multiple programs, each program has its own directive which is a keyword in brackets for example [mysqld], [client], [mysqld-safe]
mysql> explain select * from users \Gwhen tuning SQL
add indexes
changing the size and type of data fields
adding new data fields
moving indexed fields out of functions
limiting the use of temporary tables
batching expensive and/or frequent queries
periodically calculating frequent queries
If possible you try and not use temporary unless they are small, there are several ways to optimize you SQL code not to use temporary tables
try and get rid of any order by or group by, this may be done by splitting the query into two queries, it may be possible to combine the queries by using union so that intermediate results do not need to be stored in a temporary table.
again distinct may cause a creation of a temporary table, use the method above to over come this problem
if the sql_calc_found_rows keyword is used the number of rows is stored in a temporary table, it better to store the count in a table and periodically read this table for the results
the sql_small_result keyword is used in select statements, this tells the optimizer that the result set is small and thus to use a temporary table.
when using order by or group by try changing the ordering or as stated above try to get rid of it
