Skip navigation links

MySQL Forums :: Quality Assurance :: Performance query using functions


Advanced Search

Performance query using functions
Posted by: Simon Redwood ()
Date: May 03, 2012 07:10AM

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)

Options: ReplyQuote


Subject Views Written By Posted
Performance query using functions 1503 Simon Redwood 05/03/2012 07:10AM


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.