List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 9 1999 12:20am
Subject:Re: do the fields not in an index slow queries ?
View as plain text  
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
Thread
MySQL installationR-II3 May
  • do the fields not in an index slow queries ?chas9 May
    • Re: do the fields not in an index slow queries ?Sasha Pachev9 May
      • Storing tables in memory (Was : do the fields not in an indexslow queries ?)chas9 May
        • Storing tables in memory (Was : do the fields not in an indexslow queries ?)Michael Widenius11 May
    • PHP and last inserted row: Never MindVan9 May
  • MySQL installationJani Tolonen11 May