[Asterisk-Users] OT : For the SQL gurus..

[Ernest W. Lessenger]

At 11:07 AM 11/10/2003, you wrote:
>Thanks everyone for your help on this..
>
>For those who are interested I have done some speed tests on these two
>queries (below) on my server and the results are..
>
>Test script of 1000 quieries..
>Query1 ("code" field not indexed) = 47.183s
>Query1 ("code" field indexed) = 45.731s
>Query2 ("code" field not indexed) = 109.321s
>Query2 ("code" field indexed) = 2.302s
>
>Query2 has additional overhead in the script as well because it has to
>itterate through the number and build up the query..
>
>Query1 is far simpler to use in a script becasue the query does not have
>to be built up..

Since you only need to do a simple lookup, why not either (a) build your 
own db or (b) use berkely DB or some other fast database engine? Since all 
you really need to do is a prefix search on a key:

struct node {
         char num;
         struct node* p0;
         struct node* p1;
         struct node* p2;
         struct node* p3;
         struct node* p4;
         struct node* p5;
         struct node* p6;
         struct node* p7;
         struct node* p8;
         struct node* p9;
         char* desc;
}

That's 48 bytes per record (not counting the description). Memory usage 
will depend on how much data you need to store, but lookups would be O(k), 
where k is the length of the key.

--Ernest