Functional indexes are way to build indexes on values derived from rather than values in columns, or parts there of. Pretend your very price sensitive customers want to buy products where the combined price of the product and the shipping on that product are below a certain amount.
CREATE index cost_and_shipping ON products((cost + shipping));
Please note the extra () around the calculation on the value to be indexed. You will need them to create the functional index. So lets run a quick query and then look at the output from EXPLAIN to determine if the new index was used;
select *
from products
where cost + shipping = 3; +----+------+----------+----------+ | id | cost | shipping | name | +----+------+----------+----------+ | 2 | 2 | 1 | Dohickey | | 3 | 2 | 1 | Whatsits | +----+------+----------+----------+ 2 rows in set (0.0009 sec)
EXPLAIN FORMAT=TREE
select *
from products
where cost + shipping = 3\G *************************** 1. row *************************** EXPLAIN: -> Index lookup on products using cost_and_shipping ((cost + shipping)=3) (cost=0.70 rows=2) 1 row in set (0.0011 sec)
And yes, the optimizer can use the cost_and_shipping index.
However, please beware that you have work with the index as it is designed. In this case it works for cost plus shipping, not shipping plus cost. Mix the order and your query will not be able to utilize the index.
EXPLAIN FORMAT=TREE
select *
from products
where shipping + cost= 3\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((products.shipping + products.cost) = 3) (cost=0.65 rows=4)
-> Table scan on products (cost=0.65 rows=4)
1 row in set (0.0013 sec)
So, please take advantage of MySQL's functional index ability but make sure you use your indexes as you define them.
