a query not using index

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 | Öйú | ºÓÄÏ | | =C1=
ªÍ=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@m.gmane.o rg

Re: a query not using index

--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

> 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=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@tuxera.b=
e
>
>


--=20
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--

Re: a query not using index

ÔÚ 2010Äê11ÔÂ9ÈÕ ÏÂÎç3:51£¬Johan De Meersman egivamp@tuxera.be> дµÀ£º
> 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 | Öйú | ºÓÄÏ | | =C1=
ªÍ=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@m.gmane.o rg

Re: a query not using index

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;
>=20
> Magic will happen.
>=20
>=20
> 2010/11/9 wroxdb
>=20
>> Hello,
>>=20
>> I have a query below:
>>=20
>> mysql> select * from ip_test where 3061579775 between startNum and =
endNum;
>> +------------+------------+---------+----------+------+----- ---+
>> | startNum | endNum | country | province | city | isp |
>> +------------+------------+---------+----------+------+----- ---+
>> | 3061514240 | 3061579775 | Öйú | ºÓÄÏ | =
| ÁªÍ¨ |
>> +------------+------------+---------+----------+------+----- ---+
>>=20
>>=20
>> the "desc" shows it isn't using the index:
>>=20
>> mysql> desc select * from ip_test where 3061579775 between startNum =
and
>> endNum;
>>=20
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> | id | select_type | table | type | possible_keys | key | =
key_len
>> | ref | rows | Extra |
>>=20
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
>> | NULL | 396528 | Using where |
>>=20
>> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>> 1 row in set (0.01 sec)
>>=20
>>=20
>> the table structure is:
>>=20
>> 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
>>=20
>>=20
>>=20
>> please help, thanks in advance.
>>=20
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.be
>>=20
>>=20
>=20
>=20
> --=20
> 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@m.gmane.o rg

Re: a query not using index

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 |




ÔÚ 2010Äê11ÔÂ9ÈÕ ÏÂÎç5:20£¬Aveek Misra yahoo-inc.com> дµÀ£º
> 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@m.gmane.o rg

Re: a query not using index

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.
>=20
> 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 |
> =
+----+-------------+---------+------+-----------------+----- -+---------+--=
----+--------+-------------+
>=20
>=20
> 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 |
>=20
>=20
>=20
>=20
> ÔÚ 2010Äê11ÔÂ9ÈÕ ÏÂÎç5:20£¬Aveek Misra =
дµÀ£º
>> 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).
>>=20


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

Re: a query not using index

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@m.gmane.org

Re: a query not using index

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 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 |
>>
>>
>>
>>
>> ÔÚ 2010Äê11ÔÂ9ÈÕ ÏÂÎç5:20£¬Aveek Misra km@yahoo-inc.com> дµÀ£º
>>> 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@pixelated.=
net
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@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@m.gmane.o rg