The query doesn"t use the specified indexes

------=_NextPart_000_0102_01CB114E.F87EC550
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Hi,

I have made an InnoDB table and I am trying to search using some keys, =
but they are not used, and the query takes a very long time.

Here is a test table:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`symbol` varchar(20) NOT NULL,
`market` varchar(20) NOT NULL,
`id_symbol` int(10) unsigned NOT NULL,
`id_market` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `symbol` (`symbol`),
KEY `market` (`market`),
KEY `id_symbol` (`id_symbol`),
KEY `id_market` (`id_market`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8

The search query is:

mysql> explain select * from test where symbol=3D'etc' order by market =
limit 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: symbol
key: symbol
key_len: 62
ref: const
rows: 1
Extra: Using where; Using filesort


The bad part is "Using filesort", and I thought that this is because it =
doesn't like varchar or char columns for indexes, so I tried to use =
columns that contain integers:

mysql> explain select * from test where id_symbol=3D2 order by id_market =
limit 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: id_symbol
key: id_symbol
key_len: 4
ref: const
rows: 1
Extra: Using where; Using filesort=20

It still uses "Using filesort" and it doesn't use the index id_market in =
the query.

So I tried to force using the indexes:

mysql> explain select * from test force index(symbol, market) where =
symbol=3D'etc'
order by market limit 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: symbol
key: symbol
key_len: 62
ref: const
rows: 1
Extra: Using where; Using filesort=20


So, no matter I do, the query doesn't want to use the specified index. =
Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug?

The current table I am testing has no records. I have also tried this on =
a table that has more than 10 million records, with exactly the same =
results.

Please tell me what can I do.

Thanks.

--
Octavian



__________ Information from ESET NOD32 Antivirus, version of virus signatur=
e database 5214 (20100621) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


------=_NextPart_000_0102_01CB114E.F87EC550--

Re: The query doesn"t use the specified indexes

Hi Octavian, all!


Octavian Rasnita wrote:
> Hi,
>=20
> I have made an InnoDB table and I am trying to search using some ke=
ys, but they are not used, and the query takes a very long time.
>=20
> Here is a test table:
>=20
> CREATE TABLE `test` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `symbol` varchar(20) NOT NULL,
> `market` varchar(20) NOT NULL,
> `id_symbol` int(10) unsigned NOT NULL,
> `id_market` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`),
> KEY `symbol` (`symbol`),
> KEY `market` (`market`),
> KEY `id_symbol` (`id_symbol`),
> KEY `id_market` (`id_market`)
> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dutf8

So you have a table with 5 columns, one being the primary key, and
separate single-column indexes on the other 4 columns.

>=20
> The search query is:
>=20
> mysql> explain select * from test where symbol=3D'etc' order by mar=
ket limit 20\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: test
> type: ref
> possible_keys: symbol
> key: symbol
> key_len: 62
> ref: const
> rows: 1
> Extra: Using where; Using filesort
>=20
>=20
> The bad part is "Using filesort",=20

No, it works as designed: What I take from the output is that it will
use the index ("key") on column "symbol" (to find all rows that conta=
in
the constant value 'etc' in that column), and then it will sort those
rows ("order by market") to return the first 20.


> and I thought that this is because it doesn't like varchar or char=
columns for indexes, so I tried to use columns that contain integers=
:
>=20
> mysql> explain select * from test where id_symbol=3D2 order by id_m=
arket limit 20\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: test
> type: ref
> possible_keys: id_symbol
> key: id_symbol
> key_len: 4
> ref: const
> rows: 1
> Extra: Using where; Using filesort=20
>=20
> It still uses "Using filesort" and it doesn't use the index id_mark=
et in the query.

This query cannot use the index on "id_market" because using that ind=
ex
would ignore the condition "id_symbol=3D2".

>=20
> So I tried to force using the indexes:
>=20
> mysql> explain select * from test force index(symbol, market) where=
symbol=3D'etc'
> order by market limit 20\G


Unless you changed your table definition, there is no index combining
these two fields - you didn't create any.

> [[...]]
>=20
> So, no matter I do, the query doesn't want to use the specified ind=
ex. Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug=
?

See above. If you expect the system to use an index on two columns, y=
ou
should first create it.

>=20
> The current table I am testing has no records. I have also tried th=
is on a table that has more than 10 million records, with exactly the=
same results.

You cannot test execution strategies on empty tables - it doesn't mak=
e
any sense.
The moment the optimizer uses statistical information ("cost estimate=
s",
aka "cost-based optimizer"), it will detect that the table is empty, =
so
there is no use in going through an index because that will not reduc=
e
the number of rows (to check) any further.

If you want to test execution strategies, you should first make sure
that your test tables contain data which are roughly realistic, with =
a
distribution of values that is roughly realistic, and that your index=
es
will provide a decent selectivity (I'd guess, at the very least you n=
eed
20 different values per column).


It is a separate question whether that sorting is critical:
You mention 10 million records, but you don't tell us the distributio=
n
of values. If there are 10,000 different values of "symbol", on avera=
ge
such a query would have to sort 1000 records, which shouldn't be too =
bad.

>=20
> Please tell me what can I do.

Apart from the hints above:
Make your mail client break long lines.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
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: The query doesn"t use the specified indexes

--0016364d3109142a7a048a2d29d6
Content-Type: text/plain; charset=ISO-8859-1

If cardinality is high (i.e large number of rows returned in the set for
your query), then mysql may need to resort to filesort.

- Ashish

2010/6/21 Octavian Rasnita

> Hi,
>
> I have made an InnoDB table and I am trying to search using some keys, but
> they are not used, and the query takes a very long time.
>
> Here is a test table:
>
> CREATE TABLE `test` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `symbol` varchar(20) NOT NULL,
> `market` varchar(20) NOT NULL,
> `id_symbol` int(10) unsigned NOT NULL,
> `id_market` int(10) unsigned NOT NULL,
> PRIMARY KEY (`id`),
> KEY `symbol` (`symbol`),
> KEY `market` (`market`),
> KEY `id_symbol` (`id_symbol`),
> KEY `id_market` (`id_market`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> The search query is:
>
> mysql> explain select * from test where symbol='etc' order by market limit
> 20\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: test
> type: ref
> possible_keys: symbol
> key: symbol
> key_len: 62
> ref: const
> rows: 1
> Extra: Using where; Using filesort
>
>
> The bad part is "Using filesort", and I thought that this is because it
> doesn't like varchar or char columns for indexes, so I tried to use columns
> that contain integers:
>
> mysql> explain select * from test where id_symbol=2 order by id_market
> limit 20\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: test
> type: ref
> possible_keys: id_symbol
> key: id_symbol
> key_len: 4
> ref: const
> rows: 1
> Extra: Using where; Using filesort
>
> It still uses "Using filesort" and it doesn't use the index id_market in
> the query.
>
> So I tried to force using the indexes:
>
> mysql> explain select * from test force index(symbol, market) where
> symbol='etc'
> order by market limit 20\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: test
> type: ref
> possible_keys: symbol
> key: symbol
> key_len: 62
> ref: const
> rows: 1
> Extra: Using where; Using filesort
>
>
> So, no matter I do, the query doesn't want to use the specified index.
> Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug?
>
> The current table I am testing has no records. I have also tried this on a
> table that has more than 10 million records, with exactly the same results.
>
> Please tell me what can I do.
>
> Thanks.
>
> --
> Octavian
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 5214 (20100621) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>

--0016364d3109142a7a048a2d29d6--