nuBuilder Forum

Posted: **Thu Dec 24, 2020 9:42 am**

The effective change in the recent commit apart from a slate of whitespace changes have changed the following SQL:

SELECT * 
        FROM zzzzsys_tab 
        INNER JOIN zzzzsys_object ON sob_all_zzzzsys_form_id = syt_zzzzsys_form_id
        WHERE syt_zzzzsys_form_id = '$i'
        GROUP BY syt_zzzzsys_form_id, syt_title
        ORDER BY syt_order;

to:

Code: Select all

SELECT DISTINCT zzzzsys_tab.*
        FROM zzzzsys_tab 
        INNER JOIN zzzzsys_object ON sob_all_zzzzsys_form_id = syt_zzzzsys_form_id
        WHERE syt_zzzzsys_form_id = '$i'
        ORDER BY syt_order;

The major change here is to restrict the resultset into keeping just one tables' fields instead of those from both but I wonder what the "ORDER by sy_order" really does since many are of the same order value.

The following may be a better bet considering optimisation:

Code: Select all

SELECT zzzzsys_tab.*
        FROM zzzzsys_tab 
        INNER JOIN zzzzsys_object ON sob_all_zzzzsys_form_id = syt_zzzzsys_form_id
        GROUP BY `zzzzsys_tab_id`, `syt_zzzzsys_form_id`
        HAVING syt_zzzzsys_form_id = '$i';

Posted: **Fri Dec 25, 2020 3:52 pm**

apmuthu wrote: The following may be a better bet considering optimisation:

Code: Select all

SELECT zzzzsys_tab.*
        FROM zzzzsys_tab 
        INNER JOIN zzzzsys_object ON sob_all_zzzzsys_form_id = syt_zzzzsys_form_id
        GROUP BY `zzzzsys_tab_id`, `syt_zzzzsys_form_id`
        HAVING syt_zzzzsys_form_id = '$i';

This changes the Tab order since there's no ORDER BY:

order.png

tabs.png

Posted: **Fri Dec 25, 2020 4:32 pm**

apmuthu wrote: I wonder what the "ORDER by sy_order" really does since many are of the same order value.

The SQL filters by syt_zzzzsys_form_id. One form has distinct sy_order values.

Posted: **Sat Dec 26, 2020 2:03 am**

Okay. I assumed that when the order was changed, the db would get rewritten for the form_id in the desired order.
Hence the following will be in order:

Code: Select all

SELECT zzzzsys_tab.*
        FROM zzzzsys_tab
        INNER JOIN zzzzsys_object ON sob_all_zzzzsys_form_id = syt_zzzzsys_form_id
        GROUP BY `zzzzsys_tab_id`, `syt_zzzzsys_form_id`
        HAVING syt_zzzzsys_form_id = '$i'
        ORDER BY syt_order;

The use of DISTINCT is to be avoided where possible and any large table on which the query is run (with appropriate indices) will prove itself in benchmark tests.

Posted: **Sat Dec 26, 2020 2:24 am**

Thanks! I committed an update to my dev repo.

nuBuilder Forum

SQL Optimisation Commit for zzzzsys_tab

SQL Optimisation Commit for zzzzsys_tab

Re: SQL Optimisation Commit for zzzzsys_tab

Re: SQL Optimisation Commit for zzzzsys_tab

Re: SQL Optimisation Commit for zzzzsys_tab

Re: SQL Optimisation Commit for zzzzsys_tab