Optimizing a Mysql table with 40 fields

1

Hello everyone! I have a table in Mysql with 40 fields, it is a system of resumes. I'm optimizing both the table and the queries executed in PHP itself.

The point is that to perform searches for specific profiles, 20 of these fields are used, ie a "heavy" query. When a particular field is not used by the user when filling in the search data, it is not included in my query. Anyway, when I set up my indexes, I saw that the maximum acceptable is 16 columns. So I'm here to ask your opinion.

Check out some of the fields in this table below:

cur_nome
cur_cpf
cur_senha
cur_nascimento
cur_estado_civil
cur_endereco
cur_estado
cur_cidade
cur_area1  (área de interesse 1) -> recebe um id de outra tabela
cur_area2  (área de interesse 2) -> recebe um id de outra tabela
cur_area3  (área de interesse 3) -> recebe um id de outra tabela
cur_habilitacao_a (carteira de habilitacao a) -> Recebe Sim ou Não
cur_habilitacao_b (carteira de habilitacao b) -> Recebe Sim ou Não
cur_habilitacao_c (carteira de habilitacao c) -> Recebe Sim ou Não
cur_habilitacao_d (carteira de habilitacao d) -> Recebe Sim ou Não
cur_habilitacao_e (carteira de habilitacao e) -> Recebe Sim ou Não

Based on a friend's suggestion in the comments below, I took this table and divided it into three, as follows:

Tabela1  com 15 campos
Tabela2  com 15 campos
Tabela3  com 10 campos

Table 2 and Table3 have the foreign key (id of Table1) as the primary key itself.

One question I had is:

Assuming all fields are submitted at one time, at the time I write the data, then I would do 3 inserts in mysql, right?

example:

sql1 = insert into Tabela1 (campos) values (valores);
sql2 = insert into Tabela2 (campos) values (valores);
sql3 = insert into Tabela3 (campos) values (valores);

In this case, for me to ensure that Table2 and Table3 will receive in their foreign keys, the primary one of Table1, I recommend that I retrieve the value of the recorded id from table1, and thus reference them in Tables 2 and 3? It would look like this:

 sql1 = insert into Tabela1 (campos) values (valores);
 $idgravado = mysqli_insert_id($conexao);                         

 sql2 = insert into Tabela2 (tab2_codigo, outros_campos) values ($idgravado, valores);
 sql3 = insert into Tabela3 (tab3_codigo, outros_campos) values ($idgravado, valores);

What do you guys tell me?

    
asked by anonymous 17.08.2016 / 16:10

1 answer

1

Dude, ideally you would create several tables for example:

  • tblDataPersonal
  • tblEndereco
  • tblExperience
  • Then you could use INNER JOIN to fetch data from other tables with the same index. Read on at article .

        
    17.08.2016 / 16:59