Problem with relate fields when creating a new record

Quote

Total posts: 1,636
07 Jun 2017 07:33

I have a section with 54K records. If i want to create a new record, it takes around 6 seconds before the entry form opens, which is quite annoying. The section_id =3 This is due to this query:

SELECT id as value, title as text

  FROM #__js_res_record

  WHERE hidden = 0 
  AND section_id = 2 
  AND type_id IN(2) 
  AND id NOT IN(SELECT record_id 
  FROM hsb0a_js_res_record_values 
  WHERE field_id = 8)

field_id =8 is a child field from another section (section_id =2)

The result is 8 records which has nothing to do with starting a new record. 2017-06-07_15-30-12

Last Modified: 18 Jun 2017

Quote

Guest
07 Jun 2017 08:17

just a question, do you have many fields for 1 records please ?

clowride

Quote

Sackgesicht VIP

Total posts: 1,636
07 Jun 2017 08:53

Only 9 fields ... 2017-06-07_16-41-06

The long query will list records which have no attached child content to the parent field. If i want to create a new record, this query should not even run.

Quote

Sergey

Total posts: 13,748
18 Jun 2017 07:57

I was trying to not run this query if it is a new article, but this query is used also to detect is there potential children or parents for attaching. If not, it hides field completely.

This is also used to show related articles in checkbox or select form element types. And this is needed even for a new article. What select type do you have?

Quote

Sergey

Total posts: 13,748
18 Jun 2017 08:05

So if I exclude this query on popup selector for example, it might show add child button although there are no children to add.

May be the best way would be to optimize query so it runs quickly? If you make suggestions what indexes to add or drop I might include it.

Login (copy)