MySQL Bugs: #32746: multi column index ignored in date range using now()

Bug #32746	multi column index ignored in date range using now()
Submitted:	26 Nov 2007 22:33	Modified:	27 Nov 2007 19:38
Reporter:	renaud houver	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.50	OS:	Linux (Red Hat Enterprise Linux Server release 5 (Tikanga))
Assigned to:		CPU Architecture:	Any

Description:
When querying on a date range where date is compared to now(), 
and a mulit column index should be used, range method is not used.  
That results in poor performance for large tables.
If comparing to hard-coded data, the range method is used. 
That was working fine in 5.0.27.

How to repeat:
How to reproduce:

create table test (
id_phot int ,
creation_date date,
credit varchar(100),
primary key (id_phot),
index (credit, creation_date)
);

insert into test (id_phot, creation_date,credit) 
values (0 ,'2007-11-10', 'AP');
insert into test (id_phot, creation_date, credit)
 values (1 ,'2007-11-19', 'AP');
insert into test (id_phot, creation_date, credit) 
values (2 ,'2007-11-20', 'AP');

explain select count(*) from test
where credit = 'AP' and
creation_date BETWEEN TIMESTAMPADD(DAY,-3,now()) and now();

Ref method is used on index. Does not it mean that only first column in index is used ? 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	test	ref	credit	credit	303	const	2	Using where; Using index

explain select count(*) from test
where credit = 'AP' and
creation_date BETWEEN '2007-11-10' and '2007-11-19';

Range method is used. 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	test	range	credit	credit	307	(null)	1	Using where; Using index

Thank you for a problem report. Please, check if the following query:

explain select count(*) from test
where credit = 'AP' and
creation_date BETWEEN date(TIMESTAMPADD(DAY,-3,now())) and date(now());

will have better plan and run faster.

That helped !!
Thanks. 
Is that a bug related to timestamp/date conversion ?

So, this is NOT a bug in optimizer. Some time after 5.0.27 implicit datatypes conversion "rules" were changed, and now column type (DATE) is converted to expression type (DATETIME), if they are different. This prvents proper index usage.

This lead to many problems already reported. None of them is optimizer bug, though. Looks like something similar to this (read http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html) works recently:

"If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed."