Mysql – Best way to store HTML dropdown options

MySQLperformancestorage-engine

I'm developing a web site for a model agency. I have a "model" table with the details about each model (this is just an example):

CREATE TABLE model {
    model_id INT PRIMARY KEY,
    name VARCHAR(100),
    hair_color_id TINYINT,
    eyes_color_id TINYINT,
    skin_color_id TINYINT,
    city_id TINYINT,
    another_id TINYINT,
    blabla_id TINYINT,
    ...
}

The hair_color_id is a FK of the following table:

CREATE TABLE hair_color {
    id INT PRIMARY KEY,
    name VARCHAR(100),
    visible TINYINT
}

The same applies to eye_color_id and most of the other fields. This way I keep my "main" table small while I can use the other tables to store the available options and list them dinamically in the HTML dropdown lists. They are also handy when adding new models to the "model" table, I don't want people can store wrongs ids either by accident or maliciously, so the constraints keeps my model table with valid data. Of course, when I want to show the models profile I have to make many many JOINs and this will happen a lot.

Having that in mind, my question is: which storage engine would you recommend me to use for these tables (eye colors, hair colors, etc)? I'm using InnoDB (because of its cache function) but I think that maybe it's better to use MEMORY storage engine. Or MyISAM because they will be practically read only. Do you think that I should change this schema and store these values in a PHP file so it's not neccesary to make the JOINs?

Best Answer

Use InnoDB. At some point, it will be the 'only' one supported. Anyway, it is actually faster in many benchmarks.

You are talking about dozens or maybe hundreds of rows in all the the tables combined. You will have trouble noticing sluggishness until you get millions of rows. "Eschew premature optimization."

On the other hand, if you have a query that is demonstrably slow, let's see EXPLAIN SELECT ... and SHOW CREATE TABLE. There could be some trivial fix for it.

Related Question