List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 1 1999 6:27pm
Subject:Re: Q> index problem.
View as plain text  
"Sung-Jin,Kim" wrote:
> 
> Hi..
> I've found some strange phenomenon on mysql 3.22.21.
> 
> I am developing GIS application and made below table.
> 
> CREATE TABLE sanghoinfo (
>   seq bigint(38) unsigned DEFAULT '0' NOT NULL auto_increment,
>   code char(2) DEFAULT '' NOT NULL,
>   name char(30) DEFAULT '' NOT NULL,
>   DO char(12) binary,
>   CITY char(12) binary,
>   GU char(10) binary,
>   DONG char(16) binary,
>   BUNGI char(6),
>   HO char(4),
>   TEL1 char(15),
>   TEL2 char(15),
>   eminx int(11) DEFAULT '0' NOT NULL,
>   eminy int(38) DEFAULT '0' NOT NULL,
>   tag int(2) DEFAULT '0' NOT NULL,
>   PRIMARY KEY (seq),
>   KEY tag (tag,code,eminx,eminy),
>   KEY idx_name (name),
>   KEY idx_tag (tag),
>   KEY idx_emin (eminx,eminy),
>   KEY key_idx (code)
> );
> 
> as you can see, index was created on tag, eminx,  eminy,name, code.
> 
> and, I examined some select queries.
> 
> first)
> mysql> explain select * from sanghoinfo where eminx > 0 and eminx <
> 290000;
> +------------+-------+---------------+----------+---------+------+-------+-------+
> 
> | table      | type  | possible_keys | key      | key_len | ref  | rows
> | Extra |
> +------------+-------+---------------+----------+---------+------+-------+-------+
> 
> | sanghoinfo | range | idx_emin      | idx_emin |    NULL | NULL | 19746
> |       |
> +------------+-------+---------------+----------+---------+------+-------+-------+
> 
> 1 row in set (0.00 sec)
>  : idx_emin key used,
> 
> second)
> mysql> explain select * from sanghoinfo where eminx > 0 and eminx <
> 300000;
> +------------+------+---------------+------+---------+------+--------+------------+
> 
> | table      | type | possible_keys | key  | key_len | ref  | rows   |
> Extra      |
> +------------+------+---------------+------+---------+------+--------+------------+
> 
> | sanghoinfo | ALL  | idx_emin      | NULL |    NULL | NULL | 158117 |
> where used |
> +------------+------+---------------+------+---------+------+--------+------------+
> 
> 1 row in set (0.00 sec)
> 
> : no key used.
> 
> the difference between first query and second query is the range of
> eminx (290000 or 300000)
> 
> I can't understand this.
> 
> Is there any *angel* to answer to me?
> 
> Have a nice day!!
> 
> from S.J.Kim (sjkim@stripped : Korea Telecom Information Technology
> Corp.)

Hi Sung-Jin

If an index is used depends not only on the declaration, but also on the data.
In your case it seems to me, that the second query will give you a very large part of the
whole table.
Indexes are only used, if they can reduce the amount of data at least to 1/3 I think (see
the manual for exact value).
So if there are not at least 158117 * 3 rows in this table, this index is skipped.

Tschau
Christian

Thread
Q> index problem.Kim Sung-Jin1 Jun
  • Re: Q> index problem.Christian Mack1 Jun