Brandon Shuey wrote:
> My question is how does mysql use indexing in the following example:
> cDate=Date_Format(Date_Time,'%Y-%m-%d') just for purposes of this example
> CREATE TABLE tblLogins(
> indxLogin int not null,
> dDate Date_Time not null,
> cDate Date not null,
> LoginTried char(20),
> PasswdTried char(20),
> PRIMARY KEY (indxLogin),
> INDEX dateIndx (cDate,LoginTried)
> SELECT dDate,LoginTried,Count(*) FROM tblLogins WHERE dDate >='1999-05-01
> 00:00:00' and dDate < '1999-05-02 00:00:00' GROUP BY
> Will mysql use the dateIndx? Shouldn't dateIndx be sorted by dDate, LoginTried and
> provide extremely quick results by quickly
> finding the matching cDate and performing the group count on only those records? Am
> I thinking of the index in the wrong way?
> Brandon Shuey
No, it will not.
For mysql is dDate a completely different column than cDate.
As you don't have a KEY on dDate it can't use one.