Re: optimized searching - need desperate help

[Ankur Saxena]

Table 1:

CREATE TABLE Registry(MDN BIGINT NOT NULL, ESN VARCHAR(16) NOT NULL, MIN BIGINT NOT NULL, SID INT NOT NULL, ORDER BY MDN);

Table 2:
CREATE TABLE Info(SID INT NOT NULL, IP VARCHAR(32) NOT NULL ORDER BY SID);


Before I had

Table 1:

CREATE TABLE Registry(MDN VARCHAR(16) NOT NULL, ESN VARCHAR(16) NOT NULL, MIN VARCHAR(16) NOT NULL, SID VARCHAR(4) NOT NULL);

Table 2:
CREATE TABLE Info(SID VARCHAR(4) NOT NULL, IP VARCHAR(32) NOT NULL);

The only diff in a query like

Select IP from Info where SID=(Select SID from Registry where MDN='xxx');

is from .84 or .64 to about .5 in a total size of registry = 610,000 entries

I want this number to really reduce, I wouldnt think that once the numbers are arranged in order the query like this should take more than 10-15 steps to find it the correct one, which infact shouldnt take more than few millisecond.

Can you suggest something.