tag:blogger.com,1999:blog-4614960018934857975.post6651102906502011665..comments2023-11-27T09:32:36.643+00:00Comments on Mablomy: MySQL 5.7: Optimizer finds best index by expression Unknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-4614960018934857975.post-84438795892903106372016-04-05T09:11:32.695+01:002016-04-05T09:11:32.695+01:00Good point. Your example is even simpler without t...Good point. Your example is even simpler without the aggregation function.<br />I also tested ... WHERE dy*dx=221; so reverse order of factors. That is mathematically the same but the optimizer does not recognize the expression. However I doubt that is a big issue. If your application is really creating both types of expressions either create both virtual columns with indexes (probably costly) or use the Query Rewrite Plugin.Mario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-58146787890593123582016-04-04T21:35:47.160+01:002016-04-04T21:35:47.160+01:00The aggregate function is indeed not using an inde...The aggregate function is indeed not using an index, but also something simpler like this is not (yet) using the index:<br /><br />CREATE TABLE squares (dx int unsigned, dy int unsigned);<br />insert into squares select round(rand()*100,0), round(rand()*100,0);<br />insert into squares select round(rand()*100,0), round(rand()*100,0) from squares; -- until COUNT(*) == 2097152<br />ALTER TABLE squares ADD COLUMN (area INT AS (dx*dy));<br />ALTER TABLE squares ADD INDEX (area);<br />ALTER TABLE squares ADD INDEX `dx__area` (dx, area);<br />EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G<br />EXPLAIN SELECT dx*dy FROM squares WHERE dx=5\G<br />EXPLAIN FORMAT=JSON SELECT dx*dy FROM squares WHERE dx=5\G<br />EXPLAIN FORMAT=JSON SELECT area FROM squares WHERE dx=5\GDaniël van Eedenhttps://www.blogger.com/profile/14757324605223498151noreply@blogger.com