DROP FUNCTION IF EXISTS `f6`;
DELIMITER |
CREATE FUNCTION `f6`(y DATE) RETURNS DATE
DETERMINISTIC—for test
BEGIN  
      RETURN y;
END;|
DELIMITER ;

DROP FUNCTION IF EXISTS `f4`;
DELIMITER |
CREATE FUNCTION `f4`(network_id BIGINT) RETURNS BIGINT
DETERMINISTIC—for test
BEGIN  
      RETURN network_id;
END;|
DELIMITER ;mysql> select count(*) from t ;
+—————-+
| count(*)  |
+—————-+
| 276753210 |
+—————-+
1 row in set (0.01 sec)

select event_date,network_id from t where f4(40185)=network_id limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-09-01 |    40185 |
+——————+——————+
1 row in set (0.54 sec)

select event_date,network_id from t where event_date=f6(‘2010-12-29’) limit 1;
cost much more time

Why? Cause by DATE ?

Hello Ares,

Yes, DATE seems to be the reason in this case.

Best greetings,

Dominik

Why?
What difference between date and other data type?

Hi,

User defined functions are not supported, i.e. they may work (especially after your code change), but were never tested too deeply and their performance is random (in particular, not optimized in any way by Infobright query optimizer). Use at your own risk.

Regards,

Hi !

Well, the two queries have different conditions, so they may behave differently.

You may enable query execution log, then run
select event_date,network_id from t where event_date=f6(‘2010-12-29’) limit 1;
and
select event_date,network_id from t where event_date=‘2010-12-29’ limit 1;
and
and select event_date,network_id from t where f4(40185)=network_id limit 1;

to see what differences are in the execution in bh.err.

Thanks, Janusz Borkowski

I notice that the performance is releation with the data distribution in my test case.

mysql> select event_date,network_id from q_inventory_daily where f6(‘2010-09-01’)=event_date limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-09-01 |    96313 |
+——————+——————+
1 row in set (0.86 sec)

mysql> select event_date,network_id from q_inventory_daily where f6(‘2010-12-31’)=event_date limit 1;
+——————+——————+
| event_date | network_id |
+——————+——————+
| 2010-12-31 |    40185 |
+——————+——————+
1 row in set (28 min 22.01 sec)

all the data load from 2010-09-01 to 2010-12-31

The native function DATE had been optimized?

HI!

Only constant expressions are optimized.

Thanks.

If I must use stored function and without much more performance decrease,
Can u give me some advice?

Hi !

Stored functions are not supported at all, so it is difficult to advice anything. I would concentrate on the statement “I must use stored functions”. Maybe this priority can be changed…