List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:January 24 2002 2:43pm
Subject:Re: Is my table good indexed ?
View as plain text  
You have a function on a field in the where clause, so no index can be used.
Do your date_add() on the NOW() and compare it directly to the field.

Ireneusz Piasecki wrote:

>Hi folks.
>I have table:
>
>mysql> describe gosc;
>+-------------+---------------------+------+-----+---------------------+----
>---+
>| Field       | Type                | Null | Key | Default             |
>Extra |
>+-------------+---------------------+------+-----+---------------------+----
>---+
>| sesja       | varchar(32) binary  |      | PRI |                     |
>|
>| host        | varchar(80)         | YES  | MUL | NULL                |
>|
>| data_wej    | datetime            |      | MUL | 0000-00-00 00:00:00 |
>|
>| browser     | varchar(100)        | YES  |     | NULL                |
>|
>| last_action | datetime            |      | MUL | 0000-00-00 00:00:00 |
>|
>| id_pory1    | int(3) unsigned     | YES  |     | NULL                |
>|
>| id_pory2    | int(3) unsigned     | YES  |     | NULL                |
>|
>| nr_ip       | varchar(15)         | YES  |     | NULL                |
>|
>| id_user     | bigint(12) unsigned |      | MUL | 0                   |
>|
>| user_log    | varchar(150)        |      |     | 0-                  |
>|
>+-------------+---------------------+------+-----+---------------------+----
>---+
>10 rows in set (1.39 sec)
>
>
>I made:
>
>mysql> explain select sesja,data_wej,id_user,user_log,nr_ip from gosc where
>date_add(last_action, interval 5 minute) > now();
>+-------+------+---------------+------+---------+------+-------+------------
>+
>| table | type | possible_keys | key  | key_len | ref  | rows  | Extra
>|
>+-------+------+---------------+------+---------+------+-------+------------
>+
>| gosc  | ALL  | NULL          | NULL |    NULL | NULL | 37803 | where used
>|
>+-------+------+---------------+------+---------+------+-------+------------
>+
>
>Is my table good indexed ?? Or maybe this query above can be faster ??
>
>Regards
>
>Ireneusz Piasecki
>
>
>


Thread
Re: Is my table good indexed ?Unknown Sender24 Jan
  • Re: Is my table good indexed ?Gerald Clark24 Jan