Great idea! Unfortunately: mysql> SELECT MAX(ip_end - ip_start) FROM ip2org; +------------------------+ | MAX(ip_end - ip_start) | +------------------------+ | 100663295 | +------------------------+ 1 row in set (12.24 sec)
-----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]
