Optimizing a Mysql table with 40 fields

Question

Hello, people! I have a table in Mysql with 40 fields, it is a system of resumes. I am optimizing both the table and the queries performed in PHP.

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

Check below some of the fields in this table:

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 the suggestion of a friend in the comments below, I took this table and divided it into three, being as follows:

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

Table 2 and Table 3 have as foreign key (Table id 1) the primary key itself.

One question I’ve had is this::

Assuming all fields are submitted at once, at the time I save 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, to ensure that Table2 and Table3 will receive in their foreign keys, the primary of Table1, it is recommended that I redeem the value of the recorded id of Tabela1, and so make your reference 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 say, folks?

Answer 1

Dude, the ideal would be for you to create multiple tables for example:

1. tblDadosPersonals
2. tblEndereco
3. tblExperience

So later you could use the INNER JOIN to search for data in other tables with the same index. Read this article.