Retrieving closest date to input date

Question

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?

Is `fp_v2_fp_basic_prices.p_date` indexed? What does `explain select ...` say? — Schwern, Nov 14 '18 at 21:00
@Schwern I am not sure what indexed in this context means. I started using SQL 2 days ago. — Chris, Nov 14 '18 at 21:03
Index are very important for efficient SQL. Your code is very impressive for day 2. — Schwern, Nov 14 '18 at 21:12
@Schwern Thank you. I still feel like I struggle to really understand the ups and downs but I find your comment encouraging! — Chris, Nov 14 '18 at 21:18

Schwern · Answer 1 · 2018-11-14T23:07:46.760

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.

Retrieving closest date to input date

1 Answers1