Hi Octavian, all!
Octavian Rasnita wrote:
> 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
So you have a table with 5 columns, one being the primary key, and
separate single-column indexes on the other 4 columns.
>
> 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",
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 contain
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:
>
> 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.
This query cannot use the index on "id_market" because using that index
would ignore the condition "id_symbol=2".
>
> 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
Unless you changed your table definition, there is no index combining
these two fields - you didn't create any.
> [[...]]
>
> 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?
See above. If you expect the system to use an index on two columns, you
should first create it.
>
> 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.
You cannot test execution strategies on empty tables - it doesn't make
any sense.
The moment the optimizer uses statistical information ("cost estimates",
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 reduce
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 indexes
will provide a decent selectivity (I'd guess, at the very least you need
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 distribution
of values. If there are 10,000 different values of "symbol", on average
such a query would have to sort 1000 records, which shouldn't be too bad.
>
> Please tell me what can I do.
Apart from the hints above:
Make your mail client break long lines.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@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=arch...@jab.org
