This is crazy.  If someone can help me out, I'll pay them....!

A table:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ip_start | bigint(20)  |      | MUL | 0       |       |
| ip_end   | bigint(20)  |      |     | 0       |       |
| org      | varchar(80) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+

Populated with numbers for the 1st 2 fields,  about 2.9 million records

Indexes as such:
mysql> show index from ip2org;
+--------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+--------+------------+----------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| ip2org |          1 | dex      |            1 | ip_start    | A         |
2943079 |     NULL | NULL   |         |
| ip2org |          1 | dex      |            2 | ip_end      | A         |
2943079 |     NULL | NULL   |         |
+--------+------------+----------+---------
-----+-------------+-----------+-------------+----------+--------+---------+

I do this query:
mysql> explain SELECT org from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
+--------+------+---------------+------+---------+------+---------+---------
---+
| table  | type | possible_keys | key  | key_len | ref  | rows    | Extra
|
+--------+------+---------------+------+---------+------+---------+---------
---+
| ip2org | ALL  | dex           | NULL |    NULL | NULL | 2943079 | where
used |
+--------+------+---------------+------+---------+------+---------+---------
---+

And it will not use the index, but if I do this ( a count):
mysql> explain SELECT count(*) from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
+--------+-------+---------------+------+---------+------+--------+---------
----------------+
| table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra
|
+--------+-------+---------------+------+---------+------+--------+---------
----------------+
| ip2org | range | dex           | dex  |       8 | NULL | 594025 | where
used; Using index |
+--------+-------+---------------+------+---------+------+--------+---------
----------------+

It will use the index.

WHY can't I get it to use the index on a query with siple firlds with
numeric values??  The query takes about 12 seconds....   in fact when I do
the count, it still takes that long (maybe it just *thinks* it's using the
indexes !).....  this should return a value in less than  sec.... I've used
tables this big without this problem before... what's up?   No matter how a
screw around with the indexes, I can't make it work...

Help! :)




-----Original Message-----
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 11:41 PM
To: Marc Slemko
Cc: MerchantSense; [EMAIL PROTECTED]
Subject: Re: strange table speed issue

I'm not certain, but I don't think a multi-column index will help here.  The

manual is unclear on how a multi-column index is used when you are comparing

the first key part to a range rather than to a constant, but I get the 
impression it doesn't use the second key part in that case.  For you, that 
would mean your multi-column index is no better than your single column 
indexes.

The problem is that with either column, the range of matches is large enough

that the optimizer judges a table scan will be quicker than all those key 
lookups.  You can see this in the EXPLAIN output, type = ALL and rows = the 
size of your table.  Both indicate a full table scan.

You may be able to do better if you know something about the ranges defined 
by ip_start and ip_end, particularly if ip2org is relatively static.  You 
can find the size of the largest range with

   SELECT MAX(ip_end - ip_start) FROM ip2org;

Suppose that comes back with 1500.  Then the matching row will have ip_start

no less than your ip (1094799892) - 1500, and it will have ip_end no more 
than your ip + 1500.  Then

   SELECT org FROM ip2org
   WHERE ip_start BETWEEN 1094799892-1500 AND 1094799892
   AND ip_end BETWEEN 1094799892 AND 1094799892 + 1500;

specifies a small range on each column, enabling use of one index or the 
other for fast lookups.  Note that this will break for ip < 1500 or ip > 
max(ip) - 1500, but those should already use one or the other index with 
your original query.

Michael

Marc Slemko wrote:

> On Thu, 24 Jun 2004, MerchantSense wrote:
> 
> 
>>Seems ok to me...
>>
>>It seems to be checking all the rows in the explain for some reason too...
>>
>>mysql> show index from ip2org;
>>+--------+------------+----------+--------------+-------------+-----------
+-
>>------------+----------+--------+---------+
>>| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation
|
>>Cardinality | Sub_part | Packed | Comment |
>>+--------+------------+----------+--------------+-------------+-----------
+-
>>------------+----------+--------+---------+
>>| ip2org |          1 | ip_start |            1 | ip_start    | A
|
>>2943079 |     NULL | NULL   |         |
>>| ip2org |          1 | ip_end   |            1 | ip_end      | A
|
>>2943079 |     NULL | NULL   |         |
>>+--------+------------+----------+--------------+-------------+-----------
+-
>>------------+----------+--------+---------+
> 
> 
> mysql can only use one index from a particular table in any one
> query.  So if you want to do a query that uses both ip_start and
> ip_end, you would need to create a multicolumn index on ip_start,ip_end
> or vice versa.
> 
> What you have is one index on ip_start, and another on ip_end.  So
> it can use one of the indexes, but then it has to scan each row that
> matches.
> 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to