index problem

Hi
i found a strange problem . when i using index for 'select' , i
got a slower result than without index .
i have a tabe :
create table geo_query (
`id` int(10) unsigned not null auto_increment ,
`start` bigint(20) unsigned not null ,
`end` bigint(20) unsigned not null,
`desc` varchar(1000) not null,
primary key (`id`) ,
key `range` (`start`,`end`)
) engine=myisam ;
the whole table contains 430000 rows .

1, the query ' select * from geo_query where 1988778880 between
start and end ;' used 0.15 second ;
and i used 'explain' and found that it didn't use index and
scanned the whole table .
2, so i changed the query for ' select * from geo_query force
index(`range`) where 1988778880 between start and end ;' . it used
0.36 second .
i can't figure it out .why the query used index spend more time than not ?
any comment appreciate : )

--
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: index problem

The optimizer is right, you are wrong, as simple as that :-)

between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to "start >= 1988778880 and end <= 1988778880" and the optimizer should pick up the index.

Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-)


----- Original Message -----
> From: "xucheng"
> To: mysql@lists.mysql.com
> Sent: Thursday, 28 July, 2011 2:50:46 PM
> Subject: index problem
>
> Hi
> i found a strange problem . when i using index for 'select' , i
> got a slower result than without index .
> i have a tabe :
> create table geo_query (
> `id` int(10) unsigned not null auto_increment ,
> `start` bigint(20) unsigned not null ,
> `end` bigint(20) unsigned not null,
> `desc` varchar(1000) not null,
> primary key (`id`) ,
> key `range` (`start`,`end`)
> ) engine=myisam ;
> the whole table contains 430000 rows .
>
> 1, the query ' select * from geo_query where 1988778880 between
> start and end ;' used 0.15 second ;
> and i used 'explain' and found that it didn't use index and
> scanned the whole table .
> 2, so i changed the query for ' select * from geo_query force
> index(`range`) where 1988778880 between start and end ;' . it used
> 0.36 second .
> i can't figure it out .why the query used index spend more time
> than not ?
> any comment appreciate : )
>

--
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=gcdmg-mysql-2@m.gmane.org

Re: index problem

[Note to self, reply to mailinglist, not to author.... ;) ]

> i found a strange problem . when i using index for 'select' , i
> got a slower result than without index .
> i have a tabe :
> create table geo_query (
> `id` int(10) unsigned not null auto_increment ,
> `start` bigint(20) unsigned not null ,
> `end` bigint(20) unsigned not null,
> `desc` varchar(1000) not null,
> primary key (`id`) ,
> key `range` (`start`,`end`)
> ) engine=myisam ;
> the whole table contains 430000 rows .
>
> 1, the query ' select * from geo_query where 1988778880 between
> start and end ;' used 0.15 second ;
> and i used 'explain' and found that it didn't use index and
> scanned the whole table .
> 2, so i changed the query for ' select * from geo_query force
> index(`range`) where 1988778880 between start and end ;' . it used
> 0.36 second .
> i can't figure it out .why the query used index spend more time than
> not ? any comment appreciate : )

The query optimizer examined your answer, and decided a full-table scan was
faster then using an index. It estimated it would require less IO operations
to read the table in sequence in this case then reading the index & fetching
the appropriate records from the table. Turned out if was right. This is often
the case when large portions of a table (or index) could possibly matched by
the first guess. Here, the first 'guess' is that everything below start =
1988778880 is a possible match (as it's first field of the index `range`).
Every one of them has to be verified of having an `end` > your number, and has
to fetch the appropriate record if it does which is costly in harddisk IO.

BTW: as this looks as a GeoIP query, based on IP, if the `start` & `end`
ranges cannot overlap, this is probably faster:

SELECT * FROM geo_query
WHERE 1988778880 > start
ORDER BY start DESC LIMIT 1.
--
Rik Wasmus

--
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: index problem

thanks . i dropped the primary key , and it still didn't use the index .
when i dropped the index `range`, and add two indexes `start` and
`end` . it picks up the index , but it still used more seconds than
using no
index with `start` and `end` indexed as one --`range`.

2011/7/28 Johan De Meersman :
> The optimizer is right, you are wrong, as simple as that :-)
>
> between [field1] and [field2] cannot use indices, as your primary=
reference is a constant, not a field. Rewrite that to "start >=3D 19887788=
80 and end <=3D 1988778880" and the optimizer should pick up the index.
>
> Index hints are rarely ever needed. It's best to stay away from them unle=
ss you know exactly what's going on under the hood :-)
>
>
> ----- Original Message -----
>> From: "xucheng"
>> To: mysql@lists.mysql.com
>> Sent: Thursday, 28 July, 2011 2:50:46 PM
>> Subject: index problem
>>
>> Hi
>>     i found a strange problem . when i using index for 'select=
' , i
>> got a slower result than without index .
>>     i have a tabe :
>>     create table geo_query (
>>              `id` int(10) unsigned no=
t null auto_increment ,
>>              `start` bigint(20) unsig=
ned not null ,
>>              `end` bigint(20) unsigne=
d not null,
>>               `desc` varchar(1000) no=
t null,
>>               primary key (`id`) ,
>>               key `range` (`start`,`e=
nd`)
>>     ) engine=3Dmyisam ;
>>     the whole table contains 430000 rows .
>>
>>     1, the query ' select * from geo_query where 1988778880 be=
tween
>> start and end ;'  used 0.15 second ;
>>      and i used 'explain' and found that it didn't use in=
dex and
>> scanned the whole table .
>>     2, so i changed the query for ' select * from geo_query fo=
rce
>> index(`range`) where 1988778880 between start and end ;' . it used
>> 0.36 second .
>>     i can't figure it out .why the query used index spend more=
time
>>     than not ?
>>     any comment appreciate : )
>>
>
> --
> 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=3Dhellowo=
rldjerry@gmail.com
>
>

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