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: -----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]
