Yep, that's exactly what I did....
-----Original Message----- From: Sergio Salvi [mailto:[EMAIL PROTECTED] Sent: Thursday, June 24, 2004 10:55 PM To: [EMAIL PROTECTED] Subject: RE: strange table speed issue On Thu, 24 Jun 2004, MerchantSense wrote: > Yes, but I now have multi-column indexes, but still have the problem.... > It's not using the indexes at all... very strange - some sort of bug? > > Look at this: > mysql> explain SELECT org from ip2org use key (ip_start,ip_end) where > ip_start<=1094799892 and ip_end>=1094799892; > +--------+------+--------------------------+------+---------+------+-------- > -+------------+ > | table | type | possible_keys | key | key_len | ref | rows > | Extra | > +--------+------+--------------------------+------+---------+------+-------- > -+------------+ > | ip2org | ALL | ip_start,ip_end,ip_end_2 | NULL | NULL | NULL | 2943079 > | where used | > +--------+------+--------------------------+------+---------+------+-------- > -+------------+ > > With these indexes: > Oops, you forgot to paste it :) Anyway, make sure you've used the following command to create this index: alter table ip2org add index test (ip_start,ip_end); []s, Salvi. > > > -----Original Message----- > From: Michael Stassen [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 24, 2004 10:37 PM > To: Sergio Salvi > Cc: MerchantSense; [EMAIL PROTECTED] > Subject: Re: strange table speed issue > > > Sergio Salvi wrote: > > Hi! > > > > Can you send the output of the following command? > > > > show index from ip2org; > > > > It seems you don't have an index on both fields (even though it says you > > have multi-field index)... > > MUL doesn't mean part of a multi-field index. From the manual > <http://dev.mysql.com/doc/mysql/en/DESCRIBE.html>: "The MUL value indicates > that multiple occurences of a given value allowed within the field." > > > > > > > []s, > > Sergio Salvi. > > > > On Thu, 24 Jun 2004, MerchantSense wrote: > > > > > >>Hi - I need some help :) > >> > >> I set up a simple mysql table: > >> > >> mysql> describe ip2org; > >> +----------+-------------+------+-----+---------+-------+ > >> | Field | Type | Null | Key | Default | Extra | > >> +----------+-------------+------+-----+---------+-------+ > >> | ip_start | bigint(20) | | MUL | 0 | | > >> | ip_end | bigint(20) | | MUL | 0 | | > >> | org | varchar(80) | | | | | > >> +----------+-------------+------+-----+---------+-------+ > >> 3 rows in set (0.00 sec) > >> > >> It's got just over 2 million rows, and is populated with integers. > >> > >> I do this: > >> SELECT org from ip2org where ip_start<=1094799892 and > >> ip_end>=1094799892; > >> > >> and it take 12 seconds...! > >> > >> all my indexes seem to be there: > >> mysql> explain SELECT org from ip2org where ip_start<=1094799892 and > >> ip_end>=1094799892; > >> > >>| table | type | possible_keys | key | key_len | ref | rows | > Extra > >>| ip2org | ALL | ip_start,ip_end | NULL | NULL | NULL | 2943079 |where > >>used | > >> > >> is this normal? the fields are simple numbers, so it should be > >> lightning fast, no? I realize it's a big table, but it's just numbers. > >>this should come back instantly, no? > >> thanks! > >> > >> > >> > >> > >> > >> > > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
