There is a task in general: there are tables with data (simplified)

  • component table: ид, параметр1, параметр2, параметр3, значение, количество
  • parameter1 match table: ид, имя параметра, булево_значение (используем ли еще параметры для разбора) can be dozens of lines
  • Parameter 2 match table: ид, имя параметра can be tens / hundreds of lines
  • parameter 3 match table: ид, имя параметра can be hundreds / thousands of lines
  • pivot (if you can call it that) parameter table: ид, ид_параметр1, ид_параметр2, ид_параметр3
  • table of names: ид, ид_pivot_элемента, имя

When outputting values ​​from the component table, it is necessary to output the name from the table of names. The principle of building a table of titles is as follows:

  • If by name we found parameter1 in the table of correspondences of parameter1, then we check the boolean value, if true then we take into account the values ​​of parameter2 and parameter3 (if false then nothing is done)
  • Next we find the ID of the mapping between parameter2 and parameter3, look for the full set in the pivot table, if we find it, then in addition to the data from the component table we derive the name from the table of names.

How correct is such a construction of logic?
Is it correct to constantly make a SELECT from the base to check the values, or is it better to make an array with all the values ​​in advance and check through the array (since this will be in php - the memory on the array will not be much 2-4 columns per thousand values)?
Thanks in advance for your help, and I ask constructive criticism, and not just - "they do not do that"

  • The question is what is the "output of values ​​from the table". How many values ​​do you get from the database in one run of the script. It is usually expensive to fill reference directories with data every time the script is run (unless of course it is constantly running and periodically without updating reference books that it searches for something in them). And where does the data actually come from - if everything is from a database, then any data from the database can always be obtained in one call to a single query, which is much faster than using any arrays. And if you have to "constantly make a select", the possibilities of SQL are probably not being correctly used - Mike
  • I agree, perhaps I initially build the logic incorrectly. In general, everything happens in 2 stages. 1. - is loading from csv values ​​for components table. 2. - This is the output of the sample in the table of components In general, the sampling frequency is small, times per hour. import even less often. Now the final output is up to a hundred lines. "probably not correctly used SQL capabilities" I repent in sql is not strong. I thought about the options of JOINs, but did not want to complicate - Pavel Helmer
  • And what are you still optimizing. download from csv or issue data. I understand these are different tasks, since they occur with different regularity. And loading arrays from csv can and would be useful, you translate a bunch of data from names to ID probably. And when issuing data only to join, they of course complicate the request itself, but the logic in php only make a cycle and to the page - Mike
  • In general, it is necessary to not even optimize this, but to do it right away right away. when importing from csv, I just import the names, do not translate to the ID. but, at the same time, I check whether there are such names in the correspondences, if not - then the user must add the correspondence with his hands + add additional fields. I understand that it may be necessary to make an import with the conversion of names into IDs, but it is possible that the user will need to fix something in the component table with simple names that is faster and clearer (for me) than using the ID to do it. Or not? - Pavel Helmer

0