MySQL Forums
Forum List  »  InnoDB

Re: optimized searching - need desperate help
Posted by: Jay Pipes
Date: July 02, 2005 04:03PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimized searching - need desperate help
1682
July 02, 2005 04:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.