> While there are a number of good reasons to split data into separate
> tables, the reason you mention above isn't one of them. Splitting a
> table to speed indexes might be useful if you plan to query a member
> table directly (so query a subset - such as the numbers that were
> entered in a particular quarter of the year - which are all stored in a
> member table), but not if you are always accessing the parent.
> Indexes (by default) are built on the entire field - if the field is 4
> characters long, then the index will index all 4 characters. If the
> field is 32 characters, the index will index all 32 characters.
> MySQL uses a B-TREE method for creating the index. I don't think
> you'll see much of a performance difference if you split the area code
> into a separate field and use a compound index. The only time that
> would be useful is if you sometimes search for a phone number *without*
> an area code - in which case you could order the compound index with
> the 'number' and then the 'area code'. Of course, that means that the
> 'area code' indexed fields wouldn't be accessible directly - but the
> cardinality of an area code is probably low anyways...
> It is sometimes useful to perform partial indexing (in MySQL this is
> done by specifying the length to index), if the cardinality of the
> partially indexed field is close enough to the cardinality of the
> entire field, or if you have other business requirements (such as
> always perform matches of a subpart of the field) that require this.
> You can count the cardinality of a partial index using a select unique
> combined with a substring statement.
> I think the previous answer is the optimal one, use a UNIQUE index on
> the whole field, or create a PRIMARY KEY index (if nulls are allowed
> you'll have to use UNIQUE).
> P.S. - Posting through Google Groups here , which I don't think even
> includes the original message in the response ...
> Chander Ganesan
> Open Technology Group, Inc.
> Phone: 877-258-8987/919-463-0999
> Expert MySQL training