Hello,

I have a query below:

mysql> select * from ip_test where 3061579775 between startNum and endNum;
+------------+------------+---------+----------+------+----- ---+
| startNum | endNum | country | province | city | isp |
+------------+------------+---------+----------+------+----- ---+
| 3061514240 | 3061579775 | =D6=D0=B9=FA | =BA=D3=C4=CF | | =C1=
=AA=CD=A8 |
+------------+------------+---------+----------+------+----- ---+


the "desc" shows it isn't using the index:

mysql> desc select * from ip_test where 3061579775 between startNum and end=
Num;
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
1 row in set (0.01 sec)


the table structure is:

CREATE TABLE `ip_test` (
`startNum` double(20,0) default NULL,
`endNum` double(20,0) default NULL,
`country` varchar(50) NOT NULL default '',
`province` varchar(50) NOT NULL default '',
`city` varchar(50) NOT NULL default '',
`isp` varchar(100) default NULL,
KEY `startNum` (`startNum`),
KEY `endNum` (`endNum`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8



please help, thanks in advance.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

--20cf30549e61b194c804949a02f9
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Indexes typically only work on the left-hand-side. Rewrite as
select * from ip_test where startNum <=3D 3061579775 and endNum >=3D 306157=
9775;

Magic will happen.


2010/11/9 wroxdb <wroxdb [at] gmail.com>

> Hello,
>
> I have a query below:
>
> mysql> select * from ip_test where 3061579775 between startNum and endNum=
;
> +------------+------------+---------+----------+------+----- ---+
> | startNum | endNum | country | province | city | isp |
> +------------+------------+---------+----------+------+----- ---+
> | 3061514240 | 3061579775 | =D6=D0=B9=FA | =BA=D3=C4=CF | | =
=C1=AA=CD=A8 |
> +------------+------------+---------+----------+------+----- ---+
>
>
> the "desc" shows it isn't using the index:
>
> mysql> desc select * from ip_test where 3061579775 between startNum and
> endNum;
>
> +----+-------------+---------+------+-----------------+----- -+---------+-=
-----+--------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
>
> +----+-------------+---------+------+-----------------+----- -+---------+-=
-----+--------+-------------+
> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
> | NULL | 396528 | Using where |
>
> +----+-------------+---------+------+-----------------+----- -+---------+-=
-----+--------+-------------+
> 1 row in set (0.01 sec)
>
>
> the table structure is:
>
> CREATE TABLE `ip_test` (
> `startNum` double(20,0) default NULL,
> `endNum` double(20,0) default NULL,
> `country` varchar(50) NOT NULL default '',
> `province` varchar(50) NOT NULL default '',
> `city` varchar(50) NOT NULL default '',
> `isp` varchar(100) default NULL,
> KEY `startNum` (`startNum`),
> KEY `endNum` (`endNum`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
>
>
>
> please help, thanks in advance.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp [at] tuxera.b=
e
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--20cf30549e61b194c804949a02f9--

=D4=DA 2010=C4=EA11=D4=C29=C8=D5 =CF=C2=CE=E73:51=A3=ACJohan De Meersman <v=
egivamp [at] tuxera.be> =D0=B4=B5=C0=A3=BA
> Indexes typically only work on the left-hand-side. Rewrite as
> select * from ip_test where startNum <=3D 3061579775 and endNum >=3D 3061=
579775;
>

Thanks.

But this seems the same case happened:

mysql> desc select * from ip_test where startNum <=3D 3061579775 and
endNum >=3D 3061579775;
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+


And I'm sure the select can fetch records:

mysql> select * from ip_test where startNum <=3D 3061579775 and endNum
>=3D 3061579775;
+------------+------------+---------+----------+------+----- ---+
| startNum | endNum | country | province | city | isp |
+------------+------------+---------+----------+------+----- ---+
| 3061514240 | 3061579775 | =D6=D0=B9=FA | =BA=D3=C4=CF | | =C1=
=AA=CD=A8 |


Please suggest, thanks again.

Regards.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

I don't see how BETWEEN is not equivalent to (startNum <=3D and endNum =
>=3D). 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 approximate and not stored as exact values. Attempts =
to treat double values as exact in comparison may lead to the kind of =
issues that you are getting. I could be wrong though; but if Johan's =
trick does not work, you might try and change the data type to DECIMAL =
to see if it helps (or BIGINT if your numbers are not using any digits =
after the decimal since BIGINT and DOUBLE both use 8 bytes for storage).

Thanks
Aveek

On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote:

> Indexes typically only work on the left-hand-side. Rewrite as
> select * from ip_test where startNum <=3D 3061579775 and endNum >=3D =
3061579775;
>
> Magic will happen.
>
>
> 2010/11/9 wroxdb <wroxdb [at] gmail.com>
>
>> Hello,
>>
>> I have a query below:
>>
>> mysql> select * from ip_test where 3061579775 between startNum and =
endNum;
>> +------------+------------+---------+----------+------+----- ---+
>> | startNum | endNum | country | province | city | isp |
>> +------------+------------+---------+----------+------+----- ---+
>> | 3061514240 | 3061579775 | =D6=D0=B9=FA | =BA=D3=C4=CF | =
| =C1=AA=CD=A8 |
>> +------------+------------+---------+----------+------+----- ---+
>>
>>
>> the "desc" shows it isn't using the index:
>>
>> mysql> desc select * from ip_test where 3061579775 between startNum =
and
>> endNum;
>>
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> | id | select_type | table | type | possible_keys | key | =
key_len
>> | ref | rows | Extra |
>>
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
>> | NULL | 396528 | Using where |
>>
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> 1 row in set (0.01 sec)
>>
>>
>> the table structure is:
>>
>> CREATE TABLE `ip_test` (
>> `startNum` double(20,0) default NULL,
>> `endNum` double(20,0) default NULL,
>> `country` varchar(50) NOT NULL default '',
>> `province` varchar(50) NOT NULL default '',
>> `city` varchar(50) NOT NULL default '',
>> `isp` varchar(100) default NULL,
>> KEY `startNum` (`startNum`),
>> KEY `endNum` (`endNum`)
>> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
>>
>>
>>
>> please help, thanks in advance.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dvegivamp [at] tuxera.be
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

Thanks for the idea.
I have changed the datatype to bigint, the result is not changed.

mysql> desc select * from ip_test where startNum <=3D 3061579775 and
endNum >=3D 3061579775;
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+
| 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where |
+----+-------------+---------+------+-----------------+----- -+---------+---=
---+--------+-------------+


CREATE TABLE `ip_test` (
`startNum` bigint(20) NOT NULL,
`endNum` bigint(20) NOT NULL,
`country` varchar(50) NOT NULL default '',
`province` varchar(50) NOT NULL default '',
`city` varchar(50) NOT NULL default '',
`isp` varchar(100) default NULL,
KEY `startNum` (`startNum`),
KEY `endNum` (`endNum`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 |




=D4=DA 2010=C4=EA11=D4=C29=C8=D5 =CF=C2=CE=E75:20=A3=ACAveek Misra <aveekm [at] =
yahoo-inc.com> =D0=B4=B5=C0=A3=BA
> I don't see how BETWEEN is not equivalent to (startNum <=3D and endNum >=
=3D). 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 w=
hich are floating point data type. That's because floating point numbers ar=
e approximate and not stored as exact values. Attempts to treat double valu=
es as exact in comparison may lead to the kind of issues that you are getti=
ng. I could be wrong though; but if Johan's trick does not work, you might =
try and change the data type to DECIMAL to see if it helps (or BIGINT if yo=
ur numbers are not using any digits after the decimal since BIGINT and DOUB=
LE both use 8 bytes for storage).
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

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 a ANALYZE TABLE (or myismachk -a for MyISAM tables) and =
then a "SHOW INDEX" to see the cardinality information for these key =
columns.

Thanks
Aveek

On Nov 9, 2010, at 3:43 PM, wroxdb wrote:

> Thanks for the idea.
> I have changed the datatype to bigint, the result is not changed.
>
> mysql> desc select * from ip_test where startNum <=3D 3061579775 and
> endNum >=3D 3061579775;
> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
> | NULL | 396528 | Using where |
> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>
>
> CREATE TABLE `ip_test` (
> `startNum` bigint(20) NOT NULL,
> `endNum` bigint(20) NOT NULL,
> `country` varchar(50) NOT NULL default '',
> `province` varchar(50) NOT NULL default '',
> `city` varchar(50) NOT NULL default '',
> `isp` varchar(100) default NULL,
> KEY `startNum` (`startNum`),
> KEY `endNum` (`endNum`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 |
>
>
>
>
> =D4=DA 2010=C4=EA11=D4=C29=C8=D5 =CF=C2=CE=E75:20=A3=ACAveek Misra =
<aveekm [at] yahoo-inc.com> =D0=B4=B5=C0=A3=BA
>> I don't see how BETWEEN is not equivalent to (startNum <=3D and =
endNum >=3D). 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 approximate and not stored as exact values. =
Attempts to treat double values as exact in comparison may lead to the =
kind of issues that you are getting. I could be wrong though; but if =
Johan's trick does not work, you might try and change the data type to =
DECIMAL to see if it helps (or BIGINT if your numbers are not using any =
digits after the decimal since BIGINT and DOUBLE both use 8 bytes for =
storage).
>>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

Would a compound index on both startnum and endnum be a better choice?

JW

On Tuesday, November 9, 2010, Aveek Misra <aveekm [at] yahoo-inc.com> 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 indexes. Is it possible that the cardinality of the colum=
n values is so low that indexes are not being used? You could try and run a=
ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a "SHOW INDEX"=
to see the cardinality information for these key columns.
>
> Thanks
> Aveek
>
> On Nov 9, 2010, at 3:43 PM, wroxdb wrote:
>
>> Thanks for the idea.
>> I have changed the datatype to bigint, the result is not changed.
>>
>> mysql> desc select * from ip_test where startNum <=3D 3061579775 and
>> endNum >=3D 3061579775;
>> +----+-------------+---------+------+-----------------+----- -+---------+=
------+--------+-------------+
>> | id | select_type | table | type | possible_keys | key | key_len
>> | ref | rows | Extra |
>> +----+-------------+---------+------+-----------------+----- -+---------+=
------+--------+-------------+
>> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
>> | NULL | 396528 | Using where |
>> +----+-------------+---------+------+-----------------+----- -+---------+=
------+--------+-------------+
>>
>>
>> CREATE TABLE `ip_test` (
>> `startNum` bigint(20) NOT NULL,
>> `endNum` bigint(20) NOT NULL,
>> `country` varchar(50) NOT NULL default '',
>> `province` varchar(50) NOT NULL default '',
>> `city` varchar(50) NOT NULL default '',
>> `isp` varchar(100) default NULL,
>> KEY `startNum` (`startNum`),
>> KEY `endNum` (`endNum`)
>> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 |
>>
>>
>>
>>
>> =D4=DA 2010=C4=EA11=D4=C29=C8=D5 =CF=C2=CE=E75:20=A3=ACAveek Misra <avee=
km [at] yahoo-inc.com> =D0=B4=B5=C0=A3=BA
>>> I don't see how BETWEEN is not equivalent to (startNum <=3D and endNum =
>=3D). 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 a=
re approximate and not stored as exact values. Attempts to treat double val=
ues as exact in comparison may lead to the kind of issues that you are gett=
ing. I could be wrong though; but if Johan's trick does not work, you might=
try and change the data type to DECIMAL to see if it helps (or BIGINT if y=
our numbers are not using any digits after the decimal since BIGINT and DOU=
BLE both use 8 bytes for storage).
>>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny [at] pixelated.=
net
>
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny [at] pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2 [at] m.gmane.o rg

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 |
> +------------+------------+---------+----------+------+----- ---+
> | 3061514240 | 3061579775 | 中国 | 河南 | | 联通 |
> +------------+------------+---------+----------+------+----- ---+
>
>
> the "desc" shows it isn't using the index:
>
> mysql> desc select * from ip_test where 3061579775 between startNum and endNum;
> +----+-------------+---------+------+-----------------+----- -+---------+------+--------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+---------+------+-----------------+----- -+---------+------+--------+-------------+
> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
> | NULL | 396528 | Using where |
> +----+-------------+---------+------+-----------------+----- -+---------+------+--------+-------------+
> 1 row in set (0.01 sec)
>
>
> the table structure is:
>
> CREATE TABLE `ip_test` (
> `startNum` double(20,0) default NULL,
> `endNum` double(20,0) default NULL,
> `country` varchar(50) NOT NULL default '',
> `province` varchar(50) NOT NULL default '',
> `city` varchar(50) NOT NULL default '',
> `isp` varchar(100) default NULL,
> KEY `startNum` (`startNum`),
> KEY `endNum` (`endNum`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
>
>
> please help, thanks in advance.
>

Have you tried a combined index of (startnum,endnum) instead of two
single-column indexes?

You may still run into problems, though, because ranged searches are
usually performed as

WHERE column_A BETWEEN X AND Y

and not as

WHERE X BETWEEN column_A and column_B

and the optimizer has been designed to evaluate the first pattern but
not the second.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org