List:Cluster« Previous MessageNext Message »
From:Devananda Date:July 14 2004 6:24pm
Subject:Re: difficulty with indexes
View as plain text  
Thanks for the clarification :) However, I'm still not convinced that 
the index is working in the same fasion as it does for MyISAM and InnoDB 
tables. Let me explain ... today I filled this table up with 2.5mil rows 
and then did

select username from tdata order by username limit 5;

Since username is indexed I would expect this query not to take very 
long, and in a MyISAM table it doesn't, but for me with NDB, this takes 
about a full minute. You can see this same (mis)behaviour in the data I 
already sent - without the 'order by' clause the statement returns in 
0.00 sec (hehe), and when ordered by username, it takes 1.5sec. This was 
only on 2.5k rows, with no other load on the database - unacceptably slow.

Do you understand why I think there is a problem? If not, what is 
unclear in my examples, I will try to clarify.

Thank you for taking the time to respond :)

Regards,
Devananda



Michael Dykman wrote:

> this is not so much a cluster quetion, as the nature of db's and the 
> relationship to indexes in general.
>
> The use of indexs is to ease the job of searching for data.  'USE' 
> does not imply any ordering, mere a _suggested_ method of finding the 
> requested data.  'ORDER BY' remians the only ordering cluase in the 
> language.  In the first query, with no WHERE clause, there are 
> absolutely no guarantees about the order the data will come out.  The 
> USE index only promises that if 'username' is either 'WHERE' or 'ORDER 
> BY' criteria, that index will be used (instead of full table scan) to 
> produce your results.
>
>  - michael dykman
>  - michael@stripped
>
>
> Devananda wrote:
>
>> 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