Vfrdtyky

I have the following function:

DELIMITER $$

DROP FUNCTION IF EXISTS f_prevpricedate;

CREATE FUNCTION f_prevpricedate (id CHAR(8), startdate DATE)

RETURNS DATE



BEGIN



DECLARE prevpricedate DATE;



SELECT MAX(f.p_date) INTO prevpricedate

FROM fp_v2_fp_basic_prices AS f 

WHERE f.fsym_id = id AND f.p_date<startdate;



RETURN prevpricedate; 



END$$

Which basically just returns the closest date (previous date) to the input date. It runs extremely slow though, since the table is very large.

Anyone have any idea how to optimize this?

First, check what explain select ... says.

Are fp_v2_fp_basic_prices.fsym_id and fp_v2_fp_basic_prices.p_date indexed? An index allows the database to quickly match and compare rows without having to look at all of them. If there's no index it will have to compare every row in the table.

For example...

mysql> select count(*) from foo;                                                                                                                              

+----------+

| count(*) |

+----------+

|        3 |

+----------+



mysql> explain select max(this) from foo where this < 42;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | foo   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

This shows the query is doing a full table scan. It's using no key (index), the type of query is ALL, and it thinks it has to look at all 3 rows in the table.

mysql> alter table foo add index foo_this (this);

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> explain select max(this) from foo where this < 42;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

This is after adding an index. Select tables optimized away tells us the optimizer has figured out it can use the index to optimize away the whole query.

In your case you're searching by two columns, fsym_id and p_date. MySQL will only use one index per table in a query. So even if you have an index on fsym_id and an index on p_date it will only use one. To make this query perform very well you need both in a single index.

alter table fp_v2_fp_basic_prices add index(p_date, fsym_id);

This will work for queries that only use p_date as well as queries which use both p_date + fsym_id together. So you don't need an index on just p_date. But it does not cover queries which only use fsym_id. See this answer for more detail.

See also:

• https://dev.mysql.com/doc/refman/5.7/en/explain-output.html


• https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html


• http://www.mysqltutorial.org/mysql-index/


• https://www.tutorialspoint.com/mysql/mysql-indexes.htm