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 <octavian.rasn...@ssifbroker.ro> > 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 > >
