Would a compound index on both startnum and endnum be a better choice?
JW
On Tuesday, November 9, 2010, Aveek Misra wrote:
> Probably indexes need to be rebuilt using myisamchk after you changed the
> data type of the index columns. Apart from that I can't see why your query is
> not using the
On 11/8/2010 10:47 PM, wroxdb wrote:
> Hello,
>
> I have a query below:
>
> mysql> select * from ip_test where 3061579775 between startNum and endNum;
> +++-+--+--++
> | startNum | endNum | country | province | city | isp|
> +
Probably indexes need to be rebuilt using myisamchk after you changed the data
type of the index columns. Apart from that I can't see why your query is not
using the indexes. Is it possible that the cardinality of the column values is
so low that indexes are not being used? You could try and run
Thanks for the idea.
I have changed the datatype to bigint, the result is not changed.
mysql> desc select * from ip_test where startNum <= 3061579775 and
endNum >= 3061579775;
++-+-+--+-+--+-+--++-+
| id | select_type
I don't see how BETWEEN is not equivalent to (startNum <= and endNum >=). Of
course please try and let us know if that resolves the issue. But if it
doesn't, I suspect it is because the indexes are created on columns which are
floating point data type. That's because floating point numbers are a
在 2010年11月9日 下午3:51，Johan De Meersman 写道：
> Indexes typically only work on the left-hand-side. Rewrite as
> select * from ip_test where startNum <= 3061579775 and endNum >= 3061579775;
>
Thanks.
But this seems the same case happened:
mysql> desc select * from ip_test where startNum <= 30615797
Indexes typically only work on the left-hand-side. Rewrite as
select * from ip_test where startNum <= 3061579775 and endNum >= 3061579775;
Magic will happen.
2010/11/9 wroxdb
> Hello,
>
> I have a query below:
>
> mysql> select * from ip_test where 3061579775 between startNum and endNum;
> +-
Hello,
I have a query below:
mysql> select * from ip_test where 3061579775 between startNum and endNum;
+++-+--+--++
| startNum | endNum | country | province | city | isp|
+++-+--+--+
