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]
