MySQL Forums
Forum List  »  MySQL Query Browser

Re: Help me to get fast query
Posted by: kordirko kordirko
Date: January 08, 2012 05:54PM

Hello,

please show the explain of your query.
To get this, login to mysql, tee output to the log file and do "explain your_query",
then open log file and simply copy-paste results,
see how to do it in an example below:


~$ mysql -u test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.19 MySQL Community Server (GPL)

mysql> tee /tmp/log.out
Logging to file '/tmp/log.out'

mysql> explain extended
    -> select  dp.dept_no,
    ->         dp.dept_name,
    ->         emp.emp_no manager_empno,
    ->         emp.first_name manager_first_name,
    ->         emp.last_name manager_last_name,
    ->         tit.title,
    ->         sum( sal.salary ) sum_salary,
    ->         avg( sal.salary ) avg_salary
    -> from dept_manager dm
    -> join departments dp on (dm.dept_no = dp.dept_no)
    -> join employees emp on ( dm.emp_no = emp.emp_no )
    -> join dept_emp de on ( dp.dept_no = de.dept_no )
    -> left join  salaries sal  on ( de.emp_no = sal.emp_no )
    -> join titles tit on ( sal.emp_no = tit.emp_no )
    -> where sal.to_date >= curdate()
    -> group by dp.dept_no,
    ->         dp.dept_name,
    ->         manager_empno,
    ->         manager_first_name,
    ->         manager_last_name,
    ->         tit.title
    -> order by dept_no, title
    -> ;
+----+-------------+-------+--------+------------------------+-----------+---------+----------------------+-------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys          | key       | key_len | ref                  | rows  | filtered | Extra                                        |
+----+-------------+-------+--------+------------------------+-----------+---------+----------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | dp    | index  | PRIMARY                | dept_name | 42      | NULL                 |     9 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | dm    | ref    | PRIMARY,emp_no,dept_no | dept_no   | 4       | employees.dp.dept_no |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | emp   | eq_ref | PRIMARY                | PRIMARY   | 4       | employees.dm.emp_no  |     1 |   100.00 |                                              |
|  1 | SIMPLE      | de    | ref    | PRIMARY,emp_no,dept_no | dept_no   | 4       | employees.dm.dept_no | 41536 |   100.00 | Using where; Using index                     |
|  1 | SIMPLE      | tit   | ref    | PRIMARY,emp_no         | PRIMARY   | 4       | employees.de.emp_no  |     1 |   100.00 | Using index                                  |
|  1 | SIMPLE      | sal   | ref    | PRIMARY,emp_no         | PRIMARY   | 4       | employees.de.emp_no  |     4 |   100.00 | Using where                                  |
+----+-------------+-------+--------+------------------------+-----------+---------+----------------------+-------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql> show index from departments;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| departments |          0 | PRIMARY   |            1 | dept_no     | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| departments |          0 | dept_name |            1 | dept_name   | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      300584 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> quit


Options: ReplyQuote


Subject
Written By
Posted
January 08, 2012 12:59AM
January 08, 2012 12:21PM
January 08, 2012 12:28PM
Re: Help me to get fast query
January 08, 2012 05:54PM
January 08, 2012 08:25PM
January 09, 2012 08:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.