List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 15 1999 6:28pm
Subject:Re: multiple key conundrum.
View as plain text  
Hi Chas

chas wrote:
> Horrendously late thanks for the help Christian - your help and
> advice put my mind at ease and saved me potentially hours of time.

Nothing at all :)

< ... >
> >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, ...)
> 
> Yes, brain was dead at the time. Thank you.
> Btw, why do we even give these things names ?

Because you could easier assume what for it is, and also EXPLAIN is easier to read.

> >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.
> 
> OK, I managed to then make it part of a larger multiple key.
> But it does then beg the question "how do people optimise schema for
> boolean searches ?"
> (ie. for searching on columns with boolean values; for example :
> "select all product_ids from products where outofstock = 1"

Relational databases are alway bad for boolean searches.
The only thing you can do to speed this up in mysql, is to sort the table with isamchk for
one such column, or to use a SET.

> > You can check this with EXPLAIN... .
> 
> er - doesn't that only help when joining tables ?

Nope.
You can always use EXPLAIN, to see if and how a query uses KEYs.

> thank you again,
> 
> chas
< cut > 

Tschau
Christian

PS: Sorry for the late answer, I was on vacation.

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