SQL Optimisation Commit for zzzzsys_tab

[apmuthu]

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

Code: Select all

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';

[kev1n]

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

[kev1n]

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.

[apmuthu]

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.

[kev1n]

Thanks! I committed an update to my dev repo.