chas wrote:
>
> Good morning all,
>
> I have a directory of companies that I wish to store in MySQL.
> The table "companies" has over 25 fields, including :
>
> uniqueid (auto_incremented)
> name
> address1
> address2
> city
> tel
> fax
> review
> region (char(15))
> flag1 (binary 1/0)
> flag2 (binary 1/0)
> flag3 (binary 1/0)
> ...
>
> All of these fields have a one-to-one relationship
> so it makes logical sense to store them all in one
> table. However, we need to do very fast queries on
> combinations of only 5 of the fields :
> - uniqueid
> - region
> - flag1
> - flag2
> - flag3
>
> eg. select all the company id's which have flag2 and flag3
> enabled and are in a certain region.
>
> eg. select all the companies with flag1 and flag2 enabled.
>
> I can create multiple-field indexes to take care of this.
> But now I'm wondering : The other 20+ fields are only used
> when somebody wants to see the actual details for a given
> company. Would it make much (if any) difference
> to the speed of queries if I were to split the "companies"
> table into 2 tables. namely,
>
> "categories" table :
>
> uniqueid (auto_incremented)
> region (char(15))
> flag1 (binary 1/0)
> flag2 (binary 1/0)
> flag3 (binary 1/0)
>
> and
>
> "companies" table (joined on 'uniqueid')
>
> uniqueid
> name
> address1
> address2
> city
> tel
> fax
> review
> .....
>
> Or do these extra fields have absolutely no bearing on
> the speeds of queries since they are not included in the
> index ? At the moment these fields are all fixed width.
>
> If the 'review' field were of type TEXT, I presume
> the second method (ie. separating the 'categories' table
> from the 'companies' data) would be faster since
> the 'categories' table can still be fixed-length. Is this
> assumption correct ?
>
> Thank you,
>
> chas
Generally the smaller you can make your tables, the faster the queries
go. The trick to table design is to fit the entire table or a large
portion of it into RAM. RAM somewhere in the order of 1000 faster to
access than disk, so the difference between having your data entirely in
RAM and only partially, the other part being on the disk is someting the
difference between going to a store 5 minutes away to buy milk and
driving to New York from Calfornia to accomplish the same.
--
Sasha Pachev
http://www.sashanet.com
