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]
