List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 16 1999 10:25am
Subject:Re: multiple key conundrum.
View as plain text  
chas wrote:
> 
> I'm planning a table where each record has the following fields :
> 
> id          INT NOT NULL,
> category    SET  (<20 members>)  NOT NULL,
> subcategory SET  (<120 members>) NOT NULL,
> region      ENUM (<20 members>)  NOT NULL,
> rating      ENUM(-1, 0, 1, 2, 3, 4, 5, 6) DEFAULT -1  NOT NULL,
> costrating  ENUM(-1, 0, 1, 2, 3, 4, 5, 6) DEFAULT -1  NOT NULL,
> filterA     ENUM("N","Y") DEFAULT "N"  NOT NULL,
> filterB     ENUM("N","Y") DEFAULT "N"  NOT NULL,
> filterC     ENUM("N","Y") DEFAULT "N"  NOT NULL,
> filterD     ENUM("N","Y") DEFAULT "N"  NOT NULL,
> PRIMARY KEY (id),
> 
> I know that I will need to search on the following :
> 
> select id where filterA = X
> select id where filterB = X and location = Y
> select id where filterC = X and category = Y
> select id where category = X
> select id where category = X and subcategory = Y
> select id where category = X and subcategory = Y and region = Z
> select id where category = X and region = Y and filterD = Z
> 
> The problem is how to arrange the multiple keys and whether
> I should split it into several tables (at the moment, I'm
> planning on putting everything into one table).
> 
> First, is there any methodology one should follow for
> tackling this sort of problem ?
> 
> Taking the SQL select's, I've come up with :
> 
> MUTLIPLE KEY(category, subcategory, region)
> MULTIPLE KEY(region, category, filterD)
> MULTIPLE KEY(category, filterC)
> MULTIPLE KEY(location, filterB)
> MULTIPLE KEY(filterA)
> 
> But I note from the manual and archive that we shouldn't place
> too many keys or else it slows down.  With this in mind, does
> the above look right or horribly wrong ? (speed/optimisation
> being all important, disk-space is  cheap and the db isn't
> going to be over 80,000 records.)
> 
> Thank you very much,
> 
> chas

Hi Chas

First: 
You can't create column "subcategory" as SET type with 120 different members, because the
limit is 64.

Second:
More KEYs are only slowing down the INSERTs, not the SELECTs.

Third:
Your KEY don't need the MULTIPLE in front of it.
Instead I would give each KEY a name like:
KEY keyname (column1, column2, ...)

Fourth:
Your KEYs are OK, but I don't know if filterA will help you, because mysql uses only those
KEYs, that reduce the result to 1/3 of the original amout. 
As you have only two values in there, this perhaps is never used.
You can check this with EXPLAIN... .

Tschau
Christian

Thread
what's faster: a join or searching a column with the % wildcard?albert braun11 Jun
  • RE: what's faster: a join or searching a column with the % wildcDon Read11 Jun
  • Re: what's faster: a join or searching a column with the % wildcard?Benjamin Pflugmann11 Jun
  • Re: what's faster: a join or searching a column with the % wildcard?albert braun11 Jun
    • Re: what's faster: a join or searching a column with the % wildcard?Benjamin Pflugmann12 Jun
    • SET vs Join (was: what's faster: a join or searching a columnwith the % wildcard?)chas14 Jun
      • Re: SET vs Join (was: what's faster: a join or searching a column with the % wildcard?)Roger Smith14 Jun
        • Re: SET vs Join (was: what's faster: a join or searching a column with the % wildcard?)Blake Binkley15 Jun
      • Re: SET vs Join (was: what's faster: a join or searching a column with the % wildcard?)Roger Smith14 Jun
      • UpgradingRoger Smith15 Jun
        • Re: UpgradingPaul DuBois15 Jun
        • multiple key conundrum.chas15 Jun
      • Re: multiple key conundrum.Christian Mack16 Jun
        • Re: multiple key conundrum.chas27 Jun
      • Re: SET vs Join (was: what's faster: a join or searching a column with the % wildcard?)Benjamin Pflugmann20 Jun
      • Re: multiple key conundrum.Christian Mack15 Jul
  • Re: what's faster: a join or searching a column with the % wildcard?albert braun14 Jun
    • Re: what's faster: a join or searching a column with the %wildcard?Paul DuBois14 Jun