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,