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
