List:Cluster« Previous MessageNext Message »
From:Devananda Date:July 12 2004 10:20pm
Subject:difficulty with indexes
View as plain text  
It's a busy day, heh :)

I created 2 tables, the only difference between them is 'tdata' is NDB 
and 'tdata9' is MyISAM. In the create table definition I specified an 
index (i2) on a char(25) field. I inserted some data, and did "explain 
select ... use index (i2) ..." and I'm seeing something odd ... (edited 
for brevity)

mysql> show indexes from tdata;
| tdata |          1 | i2       |            1 | username    | NULL      
|        NULL |     NULL | NULL   |      | BTREE      |         |

mysql> show indexes from tdata9;
tdata9 |          1 | i2       |            1 | username    | A         
|       23976 |     NULL | NULL   |      | BTREE


mysql> explain select username from tdata use index (i2) order by 
username limit 10;
id | select_type | table | type | possible_keys | key  | key_len | ref  
| rows | Extra          |
1 | SIMPLE      | tdata | ALL  | NULL          | NULL |    NULL | NULL 
|  100 | Using filesort

mysql> explain select username from tdata9 use index (i2) order by 
username limit 10; 
id | select_type | table  | type  | possible_keys | key     | key_len | 
ref  | rows  | Extra |
1 | SIMPLE      | tdata9 | index | NULL          | PRIMARY |      25 | 
NULL | 23976 |


Can anyone give me a reason why the NDB table is using type=ALL & 
filesort, when it's got the index?
Another example of the index not being used ....... can it get any more 
index-friendly than this?

mysql> explain select username from tdata where username regexp 'aaa' 
order by username limit 10;
| id | select_type | table | type | possible_keys | key  | key_len | 
ref  | rows | Extra                       |
|  1 | SIMPLE      | tdata | ALL  | NULL          | NULL |    NULL | 
NULL |  100 | Using where; Using filesort |


And this makes absolutely no sense to me ... what is the point of an 
index if it is not ordered?

mysql> select username from tdata use index (i2) limit 5;
+-------------------+
| username          |
+-------------------+
| kbphjlmuwwqa25250 |
| uxcfycvlegse5999  |
| gnwvoypqktpb2054  |
| mhdsdxpsndvy33948 |
| sulnuhutushg45232 |
+-------------------+
5 rows in set (0.00 sec)

mysql> select username from tdata use index (i2) order by username limit 5;
+-------------------+
| username          |
+-------------------+
| aaajrvywbyta20237 |
| aaakphyyjrsi6786  |
| aaatdpuexpgh38313 |
| aabaafuwdbjw5536  |
| aabdnoqpecwy16188 |
+-------------------+
5 rows in set (1.52 sec)

Am I missing something that should be obvious? I can not figure out what 
is wrong here ...
Hope everyone's having a good day.

Cheers,
Devananda
Thread
difficulty with indexesDevananda13 Jul
Re: difficulty with indexesDevananda14 Jul
  • Re: difficulty with indexesJeremy Zawodny14 Jul
    • Re: difficulty with indexesDevananda14 Jul
      • Re: difficulty with indexesDevananda14 Jul
      • Re: difficulty with indexesJonas Oreland14 Jul