current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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      |         |               |
| coupon |          1 | coupon_fid        |            1 | coupon_fid    | A         |      219557 |     NULL | NULL   |      | BTREE      |         |               |
| coupon |          1 | name              |            1 | name          | A         |      219557 |     NULL | NULL   |      | BTREE      |         |               |
| coupon |          1 | idx_expiry_date   |            1 | expiry_date   | A         |       12915 |     NULL | NULL   | YES  | BTREE      |         |               |
| coupon |          1 | idx_created       |            1 | created       | A         |      219557 |     NULL | NULL   | YES  | BTREE      |         |               |
| coupon |          1 | idx_seo_url       |            1 | seo_url       | A         |      219557 |     NULL | NULL   |      | BTREE      |         |               |
| coupon |          1 | idx_start_date    |            1 | start_date    | A         |       43911 |     NULL | NULL   | YES  | BTREE      |         |               |
| coupon |          1 | idx_todays_coupon |            1 | todays_coupon | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| coupon |          1 | idx_counter       |            1 | counter       | A         |          63 |     NULL | NULL   | YES  | BTREE      |         |               |
| coupon |          1 | front_page        |            1 | front_page    | A         |         218 |     NULL | NULL   |      | BTREE      |         |               |
| coupon |          1 | idx_approved      |            1 | approved      | A         |         218 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


SHOW INDEX FROM store;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| store |          0 | PRIMARY     |            1 | store_id    | A         |        5237 |     NULL | NULL   |      | BTREE      |         |               |
| store |          0 | fid         |            1 | store_fid   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| store |          1 | top_store   |            1 | top_store   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| store |          1 | name        |            1 | name        | A         |        5237 |     NULL | NULL   |      | BTREE      |         |               |
| store |          1 | idx_seo_url |            1 | seo_url     | A         |        5237 |     NULL | NULL   |      | BTREE      |         |               |
| store |          1 | idx_status  |            1 | status      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| store |          1 | name_2      |            1 | name        | NULL      |           1 |     NULL | NULL   |      | FULLTEXT   |         |               |
| store |          1 | name_2      |            2 | tags        | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| store |          1 | name_2      |            3 | short_desc  | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| store |          1 | name_2      |            4 | long_desc   | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
share|improve this question
add comment

4 Answers

up vote 1 down vote

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.

share|improve this answer
add comment
up vote 1 down vote

The problem here is, that you are selecting everything from coupon. Why should MySQL have a look in the index when it has to read the whole data anyway? If you don't have to select everything from a table, never use SELECT *. Another thing is, that this data also has to be transported.

And do you know, that you don't get reliable data? When you use GROUP BY you have to adjust your SELECT clause. Every column you select should either be in the GROUP BY clause or an aggregate function should be applied to it. If you don't follow that rule, MySQL selects a random row from that group.

Fix above issues, and I'm sure 90% of your problems with that query are gone. The 10% left you can solve with compound indexes. For example an index on (coupon_id, created).

share|improve this answer
    
"Every column you select should either be in the GROUP BY clause or an aggregate function" - This isn't true, although MySQL's implementation of group by is very loose, since Coupon_ID is the primary key, there is no reason why other columns in the table can't be selected since they are functionally dependant on Coupon_ID, this is allowed by the SQL Standard. i.e. There will only ever be one value of c.Created per c.Coupon_ID so the grouping on created is implied by the grouping Coupon_ID, so the random row selected won't be random at all, there is only 1 to choose from –  GarethD Dec 19 '13 at 10:23
    
Although I do agree that using SELECT * is never a good idea. –  GarethD Dec 19 '13 at 10:24
    
I haven't said, that it can't be done, I just said, that a random row of that group will be returned. And you're wrong when stating that "there will only ever be one value of c.Created per c.Coupon_ID". –  tombom Dec 19 '13 at 10:28
    
Coupon_ID is the primary key of the coupon table, it is, by definition, unique (only 1 row per value) so hence there can only value of created per coupon_id. If you still believe I am wrong I have created an sql fiddle, I would love to see you produce two different values for created for a single coupon_ID. –  GarethD Dec 19 '13 at 10:35
    
Sorry, nevermind, guess I haven't had enough coffee yet today. –  tombom Dec 19 '13 at 10:39
add comment
up vote 0 down vote

I found that the bottleneck in the query was ORDER BY c.created DESC.

The table coupon have auto_increment PK coupon_id and the field created is also of type INT where unix timestamp is stored, So I observed that can do ORDER BY coupon_id DESC instead of created which i found executes query in 0.00 secs.

share|improve this answer
add comment
up vote 0 down vote

Ok, given that both coupon_id and store_id is a PK, i don't see the reason for the GROUP BY The FROM part select 1 row per coupon_id, the join statement joins exactly 1 (1:1 relationship) row to it, so the GROUP BY is not needed at all, so that may be one way to slightly imporve the speed

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.