Home > mysql > Tunning Mysql; Rephrase queries

Tunning Mysql; Rephrase queries

January 26th, 2009 Leave a comment Go to comments

The following is an excerpt from mysql slow log on a server which I was assigned to do MySQL tunning and optimizing. The following log was after the proper indexes were applied. Each query was taking about .9 seconds (query time is 0, because slow-query-time in mysql settings was 1, and that means logs will have an accuracy of seconds to the integer only)

# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;
# User@Host: articlec_bioeric[articlec_bioeric] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='1073534' or a.id='1073534' and a.author=p.id order by date desc limit 0,30;

So to see what was happenng I went to check out the query on the same mysql console.. which showed sort_union, using where, using temporary, using filesort .. hmm the size of data to sort is too much that it overflows the in memory table sizes. Odd.. it should be brought down! Okay funny the rows in the second table is showing all rows.. do we need all that..

desc select a.*,p.pen_name from ahm_articles a,ahm_penname p where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='927397' or a.id='927397' and a.author=p.id order by date desc limit 0,30;
+----+-------------+-------+-------------+----------------------------------------------+-----------------+---------+------+-------+---------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                | key             | key_len | ref  | rows  | Extra                                                                           |
+----+-------------+-------+-------------+----------------------------------------------+-----------------+---------+------+-------+---------------------------------------------------------------------------------+
|  1 | SIMPLE      | a     | index_merge | PRIMARY,IX_pen,IX_publish,IX_pubdate,IX_nick | IX_nick,PRIMARY | 52,4    | NULL |     2 | Using sort_union(IX_nick,PRIMARY); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ALL         | PRIMARY                                      | NULL            | NULL    | NULL | 18231 | Using where                                                                     |
+----+-------------+-------+-------------+----------------------------------------------+-----------------+---------+------+-------+---------------------------------------------------------------------------------+

On further detailed view of the structure for both the tables, I could see that the limiting and conditions were only on table a (ahm_articles). This finding helped me to rephrase the query as follows

select art.*,p.pen_name from (select * from ahm_articles where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='927397' or id='927397' order by date desc limit 0,30) as art, ahm_penname p where art.author=p.id;

Yea! Use a sub query to reduce the overheads, well that was what I saw when I asked mysql to describe the query. Now it seems promising, all the temporary and filesort has gone away, as well as the number of rows has drastically reduced. Infact the query got completed in 0.02 seconds in place of 0.9, well that is a whopping increase of performance.

desc select art.*,p.pen_name from (select * from ahm_articles where publish<>0 and publish<>2 and publish_date<='2009-01-24' and nick='927397' or id='927397' order by date desc limit 0,30) as art, ahm_penname p where art.author=p.id;
+----+-------------+--------------+--------+---------------------------------------+---------+---------+-------+------+-------+
| id | select_type | table        | type   | possible_keys                         | key     | key_len | ref   | rows | Extra |
+----+-------------+--------------+--------+---------------------------------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     |              | system | NULL                                  | NULL    | NULL    | NULL  |    1 |       |
|  1 | PRIMARY     | p            | const  | PRIMARY                               | PRIMARY | 4       | const |    1 |       |
|  2 | DERIVED     | ahm_articles | index  | PRIMARY,IX_publish,IX_pubdate,IX_nick | IX_date | 3       | NULL  |    2 |       |
+----+-------------+--------------+--------+---------------------------------------+---------+---------+-------+------+-------+

As a final note, just because this subquery here improved performace, it does not mean that subqueries will always improve performance, no it is not that way. You should always check your queries with explain on the mysql console.

Categories: mysql Tags:
  1. No comments yet.
  1. No trackbacks yet.

+ twenty seven = twenty nine