I have a company table, a real estate table and a table with real estate parameters. When choosing a property, such as an apartment for rent, the user can specify the parameters by which he is looking for it. There are two types of parameters, select and radio , i.e. something is selected from the select list, and something by clicking on radio . So that the tables are not overcrowded, I simply split the data into two tables depending on the type of parameter. Here so my select.

 SELECT names.name_ru, real_estate.price_real_estate, names.uniq_id, names.name_en, real_estate.id_real_estate FROM (company_names as names, company_real_estate as real_estate) INNER JOIN real_estate_properties_select as p_select2 on real_estate.id_real_estate = p_select2.id_real_estate INNER JOIN real_estate_properties_select as p_select4 on real_estate.id_real_estate = p_select4.id_real_estate INNER JOIN real_estate_properties_select as p_select6 on real_estate.id_real_estate = p_select6.id_real_estate INNER JOIN real_estate_properties_select as p_select7 on real_estate.id_real_estate = p_select7.id_real_estate INNER JOIN real_estate_properties_radio as p_radio0 on real_estate.id_real_estate = p_radio0.id_real_estate INNER JOIN real_estate_properties_radio as p_radio1 on real_estate.id_real_estate = p_radio1.id_real_estate INNER JOIN real_estate_properties_radio as p_radio3 on real_estate.id_real_estate = p_radio3.id_real_estate INNER JOIN real_estate_properties_radio as p_radio5 on real_estate.id_real_estate = p_radio5.id_real_estate INNER JOIN real_estate_properties_radio as p_radio8 on real_estate.id_real_estate = p_radio8.id_real_estate INNER JOIN real_estate_properties_radio as p_radio11 on real_estate.id_real_estate = p_radio11.id_real_estate INNER JOIN real_estate_properties_radio as p_radio13 on real_estate.id_real_estate = p_radio13.id_real_estate INNER JOIN real_estate_properties_radio as p_radio14 on real_estate.id_real_estate = p_radio14.id_real_estate INNER JOIN real_estate_properties_radio as p_radio25 on real_estate.id_real_estate = p_radio25.id_real_estate WHERE names.uniq_id = real_estate.uniq_id and real_estate.real_estate_type = 1 and real_estate.sale_rent = 0 and p_select2.id_propertie = 3 and p_select2.select_value = 2 and p_select4.id_propertie = 5 and p_select4.select_value = 2 and p_select6.id_propertie = 7 and p_select6.select_value = 1 and p_select7.id_propertie = 8 and p_select7.select_value = 1 and p_radio0.id_propertie = 1 and p_radio0.radio_value = 0 and p_radio1.id_propertie = 2 and p_radio1.radio_value = 1 and p_radio3.id_propertie = 4 and p_radio3.radio_value = 1 and p_radio5.id_propertie = 6 and p_radio5.radio_value = 1 and p_radio8.id_propertie = 9 and p_radio8.radio_value = 1 and p_radio11.id_propertie = 12 and p_radio11.radio_value = 1 and p_radio13.id_propertie = 14 and p_radio13.radio_value = 1 and p_radio14.id_propertie = 15 and p_radio14.radio_value = 1 and p_radio25.id_propertie = 144 and p_radio25.radio_value = 1

This is an example of the maximum parameter options when choosing a property, and so I get a response from the server TIME OUT , i.e. time for processing such selects exceeds the allowable. With less, say half the time, it works and even I would say quickly, relatively quickly. Help to optimize please this huge. I beg you, do not send me to NoSQL solutions, at this stage I can not change the architecture.

  • Why do you need so much JOIN? only one is needed here. Here the request also is hung up. And if there are no more indices ... - Alexey Shimansky
  • Be so kind as to explain or repair my select as you speak with one join. Thank you very much. - Boris

2 answers 2

I think you can try something like this:

 SELECT names.name_ru, real_estate.price_real_estate, names.uniq_id, names.name_en, real_estate.id_real_estate FROM company_names as names INNER JOIN company_real_estate as real_estate on names.uniq_id = real_estate.uniq_id INNER JOIN ( select id_real_estate from real_estate_properties_select where (id_propertie, select_value) IN((3,2),(5,2),(7,1),(8,1)) group by id_real_estate having count(1)=4 ) p_select on real_estate.id_real_estate = p_select.id_real_estate INNER JOIN ( select id_real_estate from real_estate_properties_radio where (id_propertie, radio_value) IN((1,0),(2,1),(4,1),(6,1),(9,1),(12,1),(14,1),(15,1),(144,1)) group by id_real_estate having count(1)=9 ) p_radio on real_estate.id_real_estate = p_radio.id_real_estate WHERE real_estate.real_estate_type = 1 and real_estate.sale_rent = 0

In terms of having number of specified conditions. In general, I would also look at whether it is necessary to keep separate tables for select and radio , why not put it in a single table and in a certain reference table (you probably already have it, it contains the names for id_propertie) added the field 'type the fields that would select you for selection or radio .

Plus, you need to look at the execution plans ( EXPLAIN ) for the resulting query and it is possible to make some additional indexes if there are not enough of them, or to rephrase the request.

  • Thank you so much for the answer and the idea. Thank you so much - Boris
  • oh yes, in mysql you can use IN to tuples, then it can be even easier - Eugene Bartosh
  • @EugeneBartosh Hmm. something even simpler does not occur to me. - Mike
  • Yes, thank you very much !!!! - Boris

Check for indexes (id_real_estate + id_propertie + select_value) for real_estate_properties_select and (id_real_estate + id_propertie + radio_value) for real_estate_properties_radio.

In general, it is necessary to avoid forming separate JOINs for each user (selecting something there, etc.), but I understand that you most likely have a dynamic list of properties for each group of objects, and then assigning properties to objects, therefore, differently and no options. But at least structured like this:

 SELECT names.name_ru, real_estate.price_real_estate, names.uniq_id, names.name_en, real_estate.id_real_estate FROM (company_names as names, company_real_estate as real_estate) INNER JOIN real_estate_properties_select as p_select2 on real_estate.id_real_estate = p_select2.id_real_estate and p_select2.id_propertie = 3 and p_select2.select_value = 2 INNER JOIN real_estate_properties_select as p_select4 on real_estate.id_real_estate = p_select4.id_real_estate and p_select4.id_propertie = 5 and p_select4.select_value = 2 INNER JOIN real_estate_properties_select as p_select6 on real_estate.id_real_estate = p_select6.id_real_estate and p_select6.id_propertie = 7 and p_select6.select_value = 1 INNER JOIN real_estate_properties_select as p_select7 on real_estate.id_real_estate = p_select7.id_real_estate and p_select7.id_propertie = 8 and p_select7.select_value = 1 INNER JOIN real_estate_properties_radio as p_radio0 on real_estate.id_real_estate = p_radio0.id_real_estate and p_radio0.id_propertie = 1 and p_radio0.radio_value = 0 INNER JOIN real_estate_properties_radio as p_radio1 on real_estate.id_real_estate = p_radio1.id_real_estate and p_radio1.id_propertie = 2 and p_radio1.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio3 on real_estate.id_real_estate = p_radio3.id_real_estate and p_radio3.id_propertie = 4 and p_radio3.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio5 on real_estate.id_real_estate = p_radio5.id_real_estate and p_radio5.id_propertie = 6 and p_radio5.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio8 on real_estate.id_real_estate = p_radio8.id_real_estate and p_radio8.id_propertie = 9 and p_radio8.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio11 on real_estate.id_real_estate = p_radio11.id_real_estate and p_radio11.id_propertie = 12 and p_radio11.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio13 on real_estate.id_real_estate = p_radio13.id_real_estate and p_radio13.id_propertie = 14 and p_radio13.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio14 on real_estate.id_real_estate = p_radio14.id_real_estate and p_radio14.id_propertie = 15 and p_radio14.radio_value = 1 INNER JOIN real_estate_properties_radio as p_radio25 on real_estate.id_real_estate = p_radio25.id_real_estate and p_radio25.id_propertie = 144 and p_radio25.radio_value = 1 WHERE names.uniq_id = real_estate.uniq_id and real_estate.real_estate_type = 1 and real_estate.sale_rent = 0
  • Thank you for the answer. Yes indeed, I do not have many options, well, or nosql. I will definitely check your ideas and accomplish your goal. Thanks again. - Boris