MySQL CPU Burst

Increasetmp_table_sizevalues. MySQL's configuration file,tmp_table_sizeThe default size is32M. If a temporary table exceeds that size,MySQLproduce aThe table Tbl_name is fullform of error if you do a lot of advancedGROUP byquery, Increasetmp_table_sizevalues. this isMySQLThe official explanation for this option: 
tmp_table_size 

this variable determines the maximum size for atemporary table in Memory. If the table becomes too large, a MYISAM table iscreated on disk. Try to avoid temporary tables by optimizing the queries wherepossible, but where this is not possible, try to ensure tempo Rary tables arealways stored in memory. Watching the processlist for queries with Temporarytables so take too a long to resolve can give a early warning that Tmp_table_size needs to be upped. Be aware this memory is also allocatedper-thread. An example where upping this worked for more is a server where iupped this from 32MB (the default) to 64MB with immediate Effect. The quickerresolution of queries resulted in less threads being active at all one time,with all-round benefits for the SER Ver, and available memory. 

theWHERE, Join,max (), MIN (), ORDER byThe fields used in the condition determination in the clause,The index should be built on itINDEX. The index is used to quickly find a row with a specific value on a column. No index,MySQLhave to start with the first record and then read the entire table until it finds the relevant row. The larger the table, the more time it takes. If the table has an index for the column of the query,MySQLTo quickly get to a location to find the middle of a data file, there is no need to consider all the data. If a table has +line, which is faster than the sequential reads at least -times. All theMySQLIndex(PRIMARY,UNIQUEand theINDEX)in theBstored in the tree.  

according toMySQLthe development Documentation: 

IndexIndexfor: 

quickly find a matchWHEREline of the clause 
when performing a junction(JOIN), rows are retrieved from other tables.  
find the specific index columnMAX ()orMIN ()value 
if sorting or grouping is performed on the leftmost front of an available key(For example,ORDER bykey_part_1,key_part_2), sort or group a table. If all key values are partially followedDESC, the key is read in reverse order.  

in some cases, a query can be optimized to retrieve values without consulting the data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, the values can be retrieved from the index tree for faster.  

assume that you issue the followingSELECTstatement: 

Mysql> SELECT * from Tbl_name WHERE Col1=val1and col2=val2;
If a multi-column index exists in thecol1and thecol2, the appropriate line can be removed directly. If the separate single-row column index exists in thecol1and thecol2, the optimizer tries to find a more restrictive index by deciding which index will find fewer rows and using that index to fetch rows.  

This article from the "Clear Sky" blog, declined reprint!

MySQL CPU Burst