Optimize MySQL Query using LEFT JOIN

I have a query with execution plan as below and is taking more than 20 seconds to execute how can i optimize it

EXPLAIN  SELECT c.coupon_id as c_id, `c` . *, `st`.`name` AS `store` FROM `coupon` AS `c` LEFT JOIN `store` AS `st` ON st.store_id = c.store_id GROUP BY `c`.`coupon_id` ORDER BY `c`.`created` desc LIMIT 100 OFFSET 100;
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+------+-----------------+
| id | select_type | table | type   | possible_keys | key         | key_len | ref                   | rows | Extra           |
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+------+-----------------+
|  1 | SIMPLE      | c     | index  | NULL          | idx_created | 5       | NULL                  |  200 | Using temporary |
|  1 | SIMPLE      | st    | eq_ref | PRIMARY       | PRIMARY     | 4       | sonicdevel.c.store_id |    1 |                 |
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+------+-----------------+
2 rows in set (0.02 sec)

INDEXES

```
mysql> SHOW INDEX FROM coupon;
+--------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| coupon | 0 | PRIMARY | 1 | coupon_id | A | 219557 | NULL | NULL | | BTREE | | |
| coupon | 1 | store_id | 1 | store_id | A | 16889 | NULL | NULL | | BTREE | |

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.

StackExchange Database Administrators Q#55280, answer score: 2