List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 28 1999 4:45am
Subject:Re: inefficient indexes
View as plain text  
Ask Bjoern Hansen wrote:
> 
> Running mysql 3.23.1, have a table with ~150000 rows, on FreeBSD 3.1,
> dual P2, 512MB ram etc.
> 
...
> | active |          1 | midx        |            1 | ip24        |
> | active |          1 | midx        |            2 | bpn         |
> | active |          1 | midx        |            3 | host        |
...
> 
> mysql> explain select ua, ip, banner, dart_timestamp from active where
>        ip24 = 68167168 and bpn = 952529139 and host = 0012295;
>
> +--------+------+-----------------------+------+---------+------+--------+------------+
> | table  | type | possible_keys         | key  | key_len | ref  | rows   | Extra     
> |
>
> +--------+------+-----------------------+------+---------+------+--------+------------+
> | active | ALL  | midx,host_idx,bpn_idx | NULL |    NULL | NULL | 171752 | where used
> |
>
> +--------+------+-----------------------+------+---------+------+--------+------------+
> 1 row in set (26.01 sec)

Interesting. Of the about 150,000 rows it estimates this query will
return about 171,752 rows. Maybe you should isamchk the table and try
again.

  jim...
Thread
inefficient indexesAsk Bjoern Hansen28 Jul
  • Re: inefficient indexesJim Faucette28 Jul
    • Re: inefficient indexesAsk Bjoern Hansen29 Jul