2 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

Without getting into optimizing the query itself, I'm going to suggest looking at temporary table use in your query plan. You will notice they are showing up in the explain detail under the extra column.

temp table settings in my.cnf

max_heap_table_size = ?

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

tmp_table_size = ?

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

From the PostPost

Tmp_table_size is the largest a table can be in memory when it is created automatically by a query. But this can't be larger than max_heap_table_size anyway. So there's no benefit to setting tmp_table_size greater than max_heap_table_size. It's common to set these two config variables to the same value.

How many temp tables were created and on disk ?

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+ 

Keeping that in mind, it would be helpful to know how IOs are affecting query performance. You can monitor that activity with tools like IOTOP and vmstat / vmstat how to. My point being that optimizing the query syntax is just one option among many to make your querys run faster.

Without getting into optimizing the query itself, I'm going to suggest looking at temporary table use in your query plan. You will notice they are showing up in the explain detail under the extra column.

temp table settings in my.cnf

max_heap_table_size = ?

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

tmp_table_size = ?

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

From the Post

Tmp_table_size is the largest a table can be in memory when it is created automatically by a query. But this can't be larger than max_heap_table_size anyway. So there's no benefit to setting tmp_table_size greater than max_heap_table_size. It's common to set these two config variables to the same value.

How many temp tables were created and on disk ?

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+ 

Keeping that in mind, it would be helpful to know how IOs are affecting query performance. You can monitor that activity with tools like IOTOP and vmstat / vmstat how to. My point being that optimizing the query syntax is just one option among many to make your querys run faster.

Without getting into optimizing the query itself, I'm going to suggest looking at temporary table use in your query plan. You will notice they are showing up in the explain detail under the extra column.

temp table settings in my.cnf

max_heap_table_size = ?

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

tmp_table_size = ?

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

From the Post

Tmp_table_size is the largest a table can be in memory when it is created automatically by a query. But this can't be larger than max_heap_table_size anyway. So there's no benefit to setting tmp_table_size greater than max_heap_table_size. It's common to set these two config variables to the same value.

How many temp tables were created and on disk ?

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+ 

Keeping that in mind, it would be helpful to know how IOs are affecting query performance. You can monitor that activity with tools like IOTOP and vmstat / vmstat how to. My point being that optimizing the query syntax is just one option among many to make your querys run faster.

1
source | link

Without getting into optimizing the query itself, I'm going to suggest looking at temporary table use in your query plan. You will notice they are showing up in the explain detail under the extra column.

temp table settings in my.cnf

max_heap_table_size = ?

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values.

tmp_table_size = ?

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

From the Post

Tmp_table_size is the largest a table can be in memory when it is created automatically by a query. But this can't be larger than max_heap_table_size anyway. So there's no benefit to setting tmp_table_size greater than max_heap_table_size. It's common to set these two config variables to the same value.

How many temp tables were created and on disk ?

mysql> show global status like 'Created%'; 
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 20    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 43    |
+-------------------------+-------+ 

Keeping that in mind, it would be helpful to know how IOs are affecting query performance. You can monitor that activity with tools like IOTOP and vmstat / vmstat how to. My point being that optimizing the query syntax is just one option among many to make your querys run faster.