I consider the question to be very important, as I have never met anywhere before, searching for all the values ​​on one large table in which they are all recorded.

On sites where at least some sort of filtering has been set up, the sample comes from a table where all the fields are scattered in columns and in several tables, but not dumped into one.

Now the essence of my task:

There are three tables table with users_anket user questionnaire id

here is her screen for general presentation

enter image description here

it is associated with another table of values ​​of profile_fields_value by an_id field,

and this is the profile_fields_value table profile_fields_value

enter image description here

it is associated with the third table of profile_fields field pfld_id across the pfld_id field

here is her screen

enter image description here

for sampling data from these tables I wrote the following query:

 SELECT * FROM gmcrm_users_anket table1 JOIN gmcrm_profile_fields_value table2 ON table1.an_id=table2.an_id JOIN gmcrm_profile_fields table3 ON table2.pfld_id = table3.pfld_id WHERE table1.an_status = 1

as a result, in php, I get a long array at output which I am already grouping and processing.

 Array ( [0] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 79 [pfld_id] => 1 [uid] => 0 [pfld_value] => Петров [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => last_name [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [1] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 80 [pfld_id] => 2 [uid] => 0 [pfld_value] => Петр [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => name [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [2] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 81 [pfld_id] => 3 [uid] => 0 [pfld_value] => Петровчи [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => otchestvo [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [3] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 82 [pfld_id] => 4 [uid] => 0 [pfld_value] => 1 [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => sex [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [4] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 83 [pfld_id] => 5 [uid] => 0 [pfld_value] => 10,10,1925 [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => date_of_birth [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [5] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 84 [pfld_id] => 6 [uid] => 0 [pfld_value] => Россия [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => grajdanstvo [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [6] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 85 [pfld_id] => 7 [uid] => 0 [pfld_value] => 4434 [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => series_of_pass [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [7] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 86 [pfld_id] => 8 [uid] => 0 [pfld_value] => 0884 [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0 [pfld_name] => num_of_pass [pfild_group_id] => 1 [pfild_group_name] => passport_data [pfild_group_status] => 1 ) [8] => Array ( [an_id] => 2 [an_status] => 1 [an_date] => 1534938430 [pfldv_id] => 87 [pfld_id] => 9 [uid] => 0 [pfld_value] => отделом уфмс россии [pfld_status] => 1 [p_f_m_str] => [pfldv_revision] => 0

etc

Question: How can I change the query (insert additional parameters in it to search for specific values? That is, not only where table1.an_status = 1 (this part of the sample was simple) but also

where for example name = 'Peter' and last_name = "Petrov"

I see that all the data I need is collected in one column of the profile_fields_value table profile_fields_value where the uniting field for them is an_id

I tried these options

 SELECT * FROM gmcrm_users_anket table1 JOIN gmcrm_profile_fields_value table2 ON table1.an_id=table2.an_id JOIN gmcrm_profile_fields table3 ON table2.pfld_id = table3.pfld_id WHERE table1.an_status = 1 and table1.an_id in ( SELECT sub.an_id FROM gmcrm_profile_fields_value sub WHERE (pfld_id=2 and pfld_value ='Петр') OR (pfld_id=1 and pfld_value='Петров') GROUP BY sub.an_id Having COUNT(sub.pfld_value) = 2 )

and such

 SELECT * FROM gmcrm_users_anket table1 JOIN gmcrm_profile_fields_value table2 ON table1.an_id=table2.an_id JOIN gmcrm_profile_fields table3 ON table2.pfld_id = table3.pfld_id WHERE table1.an_status = 1 and table1.an_id in ( SELECT sub.an_id FROM gmcrm_profile_fields_value sub WHERE (pfld_id, pfld_value) IN( (1, 'Петров'), (2, 'Петр') ) GROUP BY sub.an_id Having COUNT(sub.pfld_value) = 2 )

when I am looking for Petrov Peter, the request is executed 0.3s, but if I am looking for Petrov Ivan, the request has already been completed for 15 seconds. The same with Ivanov Ivan. (Request is executed for some reason, then 15 seconds)

Is it really impossible to extract the necessary group of values ​​from it with this method of storing data in a table?

I was told that this method of storage in the database would be the best, since it would take all the necessary data from one table to be faster through the nth number of related data. But so far I have not seen any benefit

this is the execution plan

enter image description here

  • one
    For multiple conditions, you get the id of the records of interest as something ru.stackoverflow.com/a/506507/194569 ( (pfld_id, pfld_value) in( (1,'Петров'), (2, 'Петр') ) ) and then to tag your request for details on them - Mike
  • in MySQL it will not work this way (dynamic number of fields), at least in 1 request without post processing - Vladimir Klykov
  • @Mike can expand the comment in the answer, I read the answer on the link. but due to lack of experience, I can’t get a normal result, maybe some or and lacks something or a sign .. - Yevgeny Shevtsov
  • It practically will not differ from the request that you were given in the answer. only it does not take into account that pfld_id needs to be checked the same, so as not to accidentally find "Peter" in some other field. As for its speed of work - here I think the question of the availability of indices. for the correct option, with the check and the type of the field and value, there should be an index of these two fields (pfld_id, pfld_value) - Mike
  • Well, you have a full scan of the table, of course it will slow down, apparently all the same index on the fields (pfld_id, pfld_value), which I said earlier you do not have - Mike

1 answer 1

Try to write your condition in a subquery, like this:

  SELECT * FROM gmcrm_users_anket table1 JOIN gmcrm_profile_fields_value table2 ON table1.an_id=table2.an_id JOIN gmcrm_profile_fields table3 ON table2.pfld_id = table3.pfld_id WHERE table1.an_status = 1 and table1.an_id in ( SELECT sub.an_id FROM gmcrm_profile_fields_value sub WHERE sub.pfld_value ='Петр' OR sub.pfld_value ="Петров" GROUP BY sub.an_id Having COUNT(sub.pfld_value) = 2 )
  • do not quite understand. I know for sure that Petrov Victor is not in the database. And if I write Petrov Petrov instead of Petrov Peter, then all the same, in response, I will receive a table with a list of fields from Petrov. How to make a search for both Petrov and Petra? - Yevgeny Shevtsov
  • then in the subquery you can add the grouping and the condition Having where you specify how many fields should match if Peter and Petrov means Having COUNT (sub.pfld_value) = 2. edited your answer above - Alexander
  • If I want to select also by date of birth, will I screw one more or and Having COUNT (sub.pfld_value) increase by 1? - Yevgeny Shevtsov
  • Yes, that's right ... - Alexander
  • the answer is not suitable, since the request takes more than 13 seconds, and this is only when searching among 13 questionnaires, I’ll imagine what happens when there are more than 1000 questionnaires - Evgeny Shevtsov