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
