> 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,
You can't create column "subcategory" as SET type with 120 different members, because the
limit is 64.
More KEYs are only slowing down the INSERTs, not the SELECTs.
Your KEY don't need the MULTIPLE in front of it.
Instead I would give each KEY a name like:
KEY keyname (column1, column2, ...)
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... .