List:General Discussion« Previous MessageNext Message »
From:Chris Trown Date:July 14 1999 5:42pm
Subject:Re: Dates and inexes
View as plain text  
On Wed, Jul 14, 1999 at 05:00:38PM +0300, sinisa@stripped scribbled:
> Chris Trown writes:
>  > 
>  > 
>  >      Today is the day for questions.
>  > 
>  >      Given:
>  > 
>  > mysql> show columns from foobar;
>  > 
>  > +------------+----------+------+-----+---------------------+-------+
>  > | Field      | Type     | Null | Key | Default             | Extra |
>  > +------------+----------+------+-----+---------------------+-------+
>  > | login      | char(40) |      | MUL |                     |       |
>  > | start_time | datetime |      | MUL | 0000-00-00 00:00:00 |       |
>  > | stop_time  | datetime |      | MUL | 0000-00-00 00:00:00 |       |
>  > +------------+----------+------+-----+---------------------+-------+
>  > 3 rows in set (0.01 sec)
>  > 
>  >      Is there any way to make month() function go faster?  For example:
>  > 
>  > select login, start_time from foobar where login='ctrown' and
> month(start_time)=7;
>  > 
>  >      shows no index when I use EXPLAIN.  For that matter, does MySQL make use
>  > of indexs on datetime types?
>  > 
>  > Chris...
>  > 
> 
> Hi Chris,
> 
> First of all, checkout number of rows. If it is small, then MySQL will 
> not use any index.
> 

    In my little test DB, I have at least 40,000 rows.

> MySQL can use index on datetime, but NEVER in a function. As soon as
> you use a function on any datatype, no index is used.q
> 

      I suspected as much with functions.

      Ok, so what would be the best approach if I need to search on a
particular year/month combo?  Would something like

select login, start_time from foobar where start_time='1999-07'

     work?

     Thanks!

Chris...

-- 
PGP fingerprint: 063FCE320681C336  78C164FC9B2F91EA
Thread
Dates and inexesChris Trown14 Jul
  • Dates and inexessinisa14 Jul
    • Re: Dates and inexesChris Trown14 Jul
      • Re: Dates and inexessinisa15 Jul
      • Re: Dates and inexesMichael Widenius30 Jul
  • Re: Dates and inexesPaul DuBois14 Jul