List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 10 1999 3:05pm
Subject:Re: Fundamental Index Question
View as plain text  
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
> dDate,LoginTried;
> 
> 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

Hi Brandon

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.

Tschau
Christian

Thread
Fundamental Index Question(Brandon Shuey)7 May
  • Re: Fundamental Index QuestionChristian Mack10 May