First of all, you need to understand how to use joins properly. The query:
Select IP from Info where SID=(Select SID from Registry where MDN='xxx');
Can be more efficiently written as an inner join:
SELECT IP FROM Info i
INNER JOIN Registry r
ON i.SID = r.SID
WHERE r.MDN='xxx';
Secondly, don't use quotes around numeric fields if your MDN field is numeric use:
SELECT IP FROM Info i
INNER JOIN Registry r
ON i.SID = r.SID
WHERE r.MDN=some_number;
Thirdly, you REALLY don't need to use BIGINT if you're only storing millions of records... there's simply no point. *Only* use BIGINTs if you plan on having more than ~4 *billion* records, which I don't think is the case here. Use the INT data type.
Fourthly, I am unsure what field you're using to actually store the phone numbers...is it the MDN or the ESN field? These field names really don't tell the developer what's stored in them...
Fifth, if you are doing consistent searches on a field, regardless of whether it is a numeric or varchar field, using an index is the best way to improve performance. Period.
So, add an index on the MDN and SID fields (because they are used in the join and where condition), if that is really how you're searching. If you have around a million records and you are executing the above query in more than a millisecond or two, I'd be surprised. To add an index on MDN and SID fields, use:
CREATE INDEX i_sid_mdn ON Registry (MDN, SID);
CREATE INDEX i_sid ON Info (SID);
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com