MySQL Forums
Forum List  »  Optimizer & Parser

profiler doesn't show me a query
Posted by: nick rulez
Date: February 19, 2011 05:48AM

Hallo. I'm beginning to study profiling and I have a strange problem.
Profiler doesn't show me a query and I can't understand why.

I post a dump to recreate the problem:

mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)

mysql> use mytest;
Database changed
mysql> create table people(
    -> id int not null auto_increment primary key,
    -> `dob` date
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> drop procedure if exists date_random //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure date_random(in low date,in upp date,in number int)
    -> begin
    -> declare i int default 0;
    -> while i < number do
    ->     begin
    ->     insert into people (`dob`)  values ( low + interval rand()* datediff(upp,low) day  );
    ->     set i = i + 1;
    ->     end;
    -> end while;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call date_random('1910-01-01',curdate(),1000);
Query OK, 1 row affected (0.08 sec)

mysql> delimiter //
mysql> create function `age`(dob date) returns int(11)
    -> no sql
    -> begin
    -> return (year(curdate())-year(dob))-(right(curdate(),5)< right(dob,5) );
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from people;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select concat_ws('-',min(year(curdate()) - year(dob) - (right(curdate(),5) < right(dob,5))),max(year(curdate()) -year(dob) - (right(curdate(),5) < right(
    ->   ) as intervallo,
    -> count(*) as totale
    -> from people
    -> group by if((year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)))=0,1,ceil( (year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)))
    -> ;
+------------+--------+
| intervallo | totale |
+------------+--------+
| 0-5        |     61 |
| 6-10       |     39 |
| 11-15      |     44 |
| 16-20      |     51 |
| 21-25      |     64 |
| 26-30      |     37 |
| 31-35      |     56 |
| 36-40      |     60 |
| 41-45      |     58 |
| 46-50      |     48 |
| 51-55      |     47 |
| 56-60      |     48 |
| 61-65      |     48 |
| 66-70      |     44 |
| 71-75      |     49 |
| 76-80      |     39 |
| 81-85      |     50 |
| 86-90      |     58 |
| 91-95      |     45 |
| 96-100     |     49 |
| 101-101    |      5 |
+------------+--------+
21 rows in set (0.01 sec)

mysql> show profiles
    -> ;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query
                                                                                                                                    |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00032800 | select count(*) from people
                                                                                                                                    |
|        2 | 0.01082575 | select concat_ws('-',min(year(curdate()) - year(dob) - (right(curdate(),5) < right(dob,5))),max(year(curdate()) -year(dob) - (right(cu
         ) as intervallo,
count(*) as totale
from people
group by if((year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)))=0,1,ceil( |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select concat_ws('-',min(age(dob)),max(age(dob))) as year,count(*) as total from people
    -> group by if(age(dob)=0,1,ceil(age(dob)/5));
+---------+-------+
| year    | total |
+---------+-------+
| 0-5     |    61 |
| 6-10    |    39 |
| 11-15   |    44 |
| 16-20   |    51 |
| 21-25   |    64 |
| 26-30   |    37 |
| 31-35   |    56 |
| 36-40   |    60 |
| 41-45   |    58 |
| 46-50   |    48 |
| 51-55   |    47 |
| 56-60   |    48 |
| 61-65   |    48 |
| 66-70   |    44 |
| 71-75   |    49 |
| 76-80   |    39 |
| 81-85   |    50 |
| 86-90   |    58 |
| 91-95   |    45 |
| 96-100  |    49 |
| 101-101 |     5 |
+---------+-------+
21 rows in set (0.10 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query
                                                                                                                                    |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00032800 | select count(*) from people
                                                                                                                                    |
|        2 | 0.01082575 | select concat_ws('-',min(year(curdate()) - year(dob) - (right(curdate(),5) < right(dob,5))),max(year(curdate()) -year(dob) - (right(cu
         ) as intervallo,
count(*) as totale
from people
group by if((year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)))=0,1,ceil( |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

As you can see profiler doesn't display this query:
select concat_ws('-',min(age(dob)),max(age(dob))) as year,count(*) as total from people
group by if(age(dob)=0,1,ceil(age(dob)/5));

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
profiler doesn't show me a query
2640
February 19, 2011 05:48AM
1352
February 19, 2011 07:29AM


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.