1

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.