Performance query using functions

[Simon Redwood]

I was looking for some insight into some performance issue I am seeing with Mysql 5.1 on CentOS 5.

I was hoping to be able to utilise the SQL functions to determine wether an order was 'live' or not based upon the value of a status field. This way I would only need update the function any codes changes or new ones get added.

My problem is that the time taken to run the query using the fuction shoots from 6 seconds to over a minute.

Is there something that I am doing wrong ??

Many thanks,
Simon


create function status_is_live(this_status varchar(1))
returns integer(1)
DETERMINISTIC
begin
return not instr('RXCI', this_status);
end

mysql> select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where peters_status_is_live(pol.status) and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----------+
| count(1) |
+----------+
| 58786 |
+----------+
1 row in set (1 min 16.49 sec)
mysql> explain select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where peters_status_is_live(pol.status) and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
| 1 | SIMPLE | pol | ALL | ORDEAN,IDX_LDAT,IDX_OUORD | NULL | NULL | NULL | 10427514 | Using where |
| 1 | SIMPLE | poh | eq_ref | PRIMARY | PRIMARY | 4 | peters.pol.ourord | 1 | Using where |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
2 rows in set (0.00 sec)




mysql> select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where not instr('RXCI', pol.status) and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----------+
| count(1) |
+----------+
| 58786 |
+----------+
1 row in set (6.37 sec)
mysql> explain select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where not instr('RXCI', pol.status) and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
| 1 | SIMPLE | pol | ALL | ORDEAN,IDX_LDAT,IDX_OUORD | NULL | NULL | NULL | 10427514 | Using where |
| 1 | SIMPLE | poh | eq_ref | PRIMARY | PRIMARY | 4 | peters.pol.ourord | 1 | Using where |
+----+-------------+-------+--------+---------------------------+---------+---------+-------------------+----------+-------------+
2 rows in set (0.00 sec)



mysql> select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where pol.status not in ('R','X','C','I') and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----------+
| count(1) |
+----------+
| 58786 |
+----------+
1 row in set (3.38 sec)

mysql> explain select count(1) from peters_ordlin as pol inner join peters_ordhed as poh on pol.ourord = poh.order_number where pol.status not in ('R','X','C','I') and pol.line_date < '2010-01-01' and poh.account not like '%STOCK%' ;
+----+-------------+-------+--------+------------------------------------+----------+---------+-------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------+----------+---------+-------------------+---------+-------------+
| 1 | SIMPLE | pol | range | ORDEAN,IDX_LDAT,IDX_OUORD,IDX_STAT | IDX_STAT | 5 | NULL | 1316262 | Using where |
| 1 | SIMPLE | poh | eq_ref | PRIMARY | PRIMARY | 4 | peters.pol.ourord | 1 | Using where |
+----+-------------+-------+--------+------------------------------------+----------+---------+-------------------+---------+-------------+
2 rows in set (0.00 sec)