MySQL Bugs: #74930: Use an index when a constant is subtracted from or added to a column

Bug #74930	Use an index when a constant is subtracted from or added to a column
Submitted:	19 Nov 2014 15:51	Modified:	19 Nov 2014 18:53
Reporter:	Victor Porton	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.5.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I suggest (if this is possible, I am not an SQL optimization expert), to make the following query to be able to be run on indexes for optimum performance:

SELECT * FROM test JOIN test2 ON test.a=test2.a-1

We assume:

CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `test2` (
  `a` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

How to repeat:
CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `test2` (
  `a` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

$ for i in $(seq 10000); do echo "INSERT test SET a=$i; INSERT test2 SET a=$i" | mysql -u... -p... test; done

EXPLAIN SELECT * FROM test JOIN test2 ON test.a=test2.a-1

Suggested fix:
Use keys instead of scanning.

Thank you very much for filing this feature request.

What you would like to see is that MySQL support indices that can be used over expressions. As our manual clearly states, we can not use index over an expression.

Solution for the problem are, so called, functional indices. MySQL has plans to develop functional indices, but the exact scheduling is yet unknown.

I am not sure, but isn't my special case of equality comparing a column with an other column which differ up to addition of a constant easier to implement than arbitrary functional indexes?

I propose `test JOIN test2 ON test.a=test2.a-1` with test.a differing of test2.a just by a constant (-1 in my case), not by an arbitrary expression.

Yes, it is quite clear to me what you propose, but for our server it is an expression, regardless of how simple it is. We can not develop a special feature just for a constant added to the column.