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
