> 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 :)
< ... >
> >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.
> >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 ?
You can always use EXPLAIN, to see if and how a query uses KEYs.
> thank you again,
< cut >
PS: Sorry for the late answer, I was on vacation.